package cn.tnar.parkservice.service.impl;

import cn.tnar.parkservice.config.Constant;
import cn.tnar.parkservice.config.ExceptionConst;
import cn.tnar.parkservice.config.ExportConstant;
import cn.tnar.parkservice.controller.employee.TollReportController;
import cn.tnar.parkservice.controller.financia.FinancialManageController;
import cn.tnar.parkservice.controller.order.TParkOrderController;
import cn.tnar.parkservice.exception.CustomException;
import cn.tnar.parkservice.model.dto.*;
import cn.tnar.parkservice.model.entity.*;
import cn.tnar.parkservice.model.request.InvoiceRequest;
import cn.tnar.parkservice.model.request.ParkWorkLogRequset;
import cn.tnar.parkservice.model.request.TParkGroupMemberinfoRequest;
import cn.tnar.parkservice.model.request.TParkVisitHistoryRequest;
import cn.tnar.parkservice.model.response.*;
import cn.tnar.parkservice.service.*;
import cn.tnar.parkservice.util.DtoUtil;
import cn.tnar.parkservice.util.ExcelUtil;
import cn.tnar.parkservice.util.common.ResultCode;
import cn.tnar.parkservice.util.common.ResultJson;
import cn.tnar.parkservice.util.common.StringUtil;
import cn.tnar.pms.kesb.KesbException;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static cn.tnar.parkservice.util.DateUtils.*;

@Service
public class ExportServiceImpl implements ExportService {
    @Autowired
    ITParkVisitHistoryService itParkVisitHistoryService;
    @Autowired
    ITParkGroupMemberinfoService itParkGroupMemberinfoService;
    @Autowired
    ITParkMemberGroupService itParkMemberGroupService;
    @Autowired
    ITParkGroupCarinfoService itParkGroupCarinfoService;
    @Autowired
    private ITParkMemberGroupRelationService itParkMemberGroupRelationService;
    @Autowired
    private ITParkVisitApplyService itParkVisitApplyService;
    @Autowired
    private ITCarMemberamountinfoService itCarMemberamountinfoService;

    @Autowired
    private TParkGroupMemberinfoServiceImpl tParkGroupMemberinfoServiceImpl;
    @Autowired
    private ITSysSnogeneralService itSysSnogeneralService;
    @Autowired
    private KesbApiService kesbApiService;
    @Autowired
    private ITCarFeesroleinfoService itCarFeesroleinfoService;
    @Autowired
    @Qualifier("memPaymentRecordServiceImpl")
    private MemPaymentRecordService memPaymentRecordService;
    @Autowired
    @Qualifier("tParkWorkLogServiceImpl")
    private ITParkWorkLogService itParkWorkLogService;

    @Autowired
    private ITDictionaryService itDictionaryService;
    @Autowired
    private ITParkInfoService itParkInfoService;

    @Autowired
    private TParkOrderController tParkOrderController;

    @Autowired
    private TollReportController tollReportController;
    @Autowired
    private ITParkGroupOperateLogService itParkGroupOperateLogService;

    @Autowired
    private ITParkGroupSeatinfoService itParkGroupSeatinfoService;
    @Autowired
    private FinancialManageController financialManageController;
    private ResultJson resultJson = new ResultJson();

    @Autowired
    private MqService mqService;

    private static final Logger log = LoggerFactory.getLogger(ExportServiceImpl.class);

    @Override
    public void getVisit(ExportDto exportDto, HttpServletResponse response) {
        ResultJson resultJson = itParkVisitHistoryService.select(exportDto.getJson());
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<TParkVisitHistoryResponse> list = ((Page) resultJson.getData()).getRecords();
            if (list.size() > 0) {
                contents = new String[list.size()][ExportConstant.visit_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).getCarId();
                    contents[i][2] = list.get(i).getVisitor();
                    contents[i][3] = list.get(i).getMobile();
                    contents[i][4] = list.get(i).getFeesroleinfoName();
                    String visitTimeStart = list.get(i).getVisitTimeStart().toString();
                    contents[i][5] = visitTimeStart.substring(0, 4) + "-" + visitTimeStart.substring(4, 6) + "-" + visitTimeStart.substring(6, 8);
                    String state = list.get(i).getVisitState().toString();
                    String stateName = "";
                    switch (state) {
                        case "0":
                            stateName = "未来访";
                            break;
                        case "1":
                            stateName = "来访中";
                            break;
                        case "2":
                            stateName = "已离开";
                            break;
                        default:
                            stateName = "";
                            break;
                    }
                    contents[i][6] = stateName;
                }
            }
        }

        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.visit_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void getWhiteBlack(ExportDto exportDto, HttpServletResponse response) {
        TParkGroupMemberinfoRequest request = JSON.parseObject(exportDto.getJson(), TParkGroupMemberinfoRequest.class);
        TParkMemberGroup byId = itParkMemberGroupService.getById(request.getMemberGroupId());
        Integer classify = byId.getClassify();
        Integer type = byId.getType();

        ResultJson resultJson = new ResultJson();
        String[][] contents = null;
        String[] title = null;
        if (type == 1) {
            resultJson = itParkGroupMemberinfoService.select(exportDto.getJson());
        } else if (type == 0) {
            resultJson = itParkGroupMemberinfoService.queryAll(exportDto.getJson());
        }
        //白名单
        if (resultJson.getCode() == 200) {
            List<Map<String, Object>> list = ((Page) resultJson.getData()).getRecords();
            if (classify == 3) {
                title = ExportConstant.white_title;
                contents = new String[list.size()][title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).get("owner_name").toString();
                    contents[i][2] = list.get(i).get("tel_no").toString();
                    contents[i][3] = list.get(i).get("car_id").toString();
                    String start_date = list.get(i).get("start_date").toString();
                    String end_date = list.get(i).get("end_date").toString();
                    contents[i][4] = start_date.substring(0, 4) + "-" + start_date.substring(4, 6) + "-" + start_date.substring(6, 8);
                    contents[i][5] = end_date.substring(0, 4) + "-" + end_date.substring(4, 6) + "-" + end_date.substring(6, 8);
                    contents[i][6] = list.get(i).get("state").toString();
                }
            } else if (classify == 4) {
                title = ExportConstant.black_title;
                contents = new String[list.size()][title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).get("car_id").toString();
                    contents[i][2] = list.get(i).get("owner_name").toString();
                    contents[i][3] = list.get(i).get("tel_no").toString();
                    contents[i][4] = Optional.ofNullable(list.get(i).get("regionNames")).orElse("").toString();
                    String start_date = list.get(i).get("start_date").toString();
                    String end_date = list.get(i).get("end_date").toString();
                    contents[i][5] = start_date.substring(0, 4) + "-" + start_date.substring(4, 6) + "-" + start_date.substring(6, 8);
                    contents[i][6] = end_date.substring(0, 4) + "-" + end_date.substring(4, 6) + "-" + end_date.substring(6, 8);
                    contents[i][7] = list.get(i).get("remark").toString();

                }
            }

        }
        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void getOther(ExportDto exportDto, HttpServletResponse response) {

        ResultJson resultJson = itParkGroupMemberinfoService.queryOtherAll(exportDto.getJson());
        TParkGroupMemberinfoRequest request = JSON.parseObject(exportDto.getJson(), TParkGroupMemberinfoRequest.class);
        Long memberGroupId = request.getMemberGroupId();
        TParkMemberGroup memberGroup = itParkMemberGroupService.getById(memberGroupId);
        Integer isFullFree = memberGroup.getIsFullFree() == null ? 0 : 1;
        String[] title = null;
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<Map<String, Object>> list = ((Page) resultJson.getData()).getRecords();
            if (list.size() > 0) {
                if (request.getType() == 4) {
                    title = ExportConstant.other_title_four;
                    contents = new String[list.size()][ExportConstant.other_title_four.length];
                } else {
                    if (memberGroup.getType() == 1 && isFullFree == 1) {
                        //当为卡证并且开启满免时
                        title = ExportConstant.other_title2;
                        contents = new String[list.size()][ExportConstant.other_title2.length];
                    } else {
                        title = ExportConstant.other_title;
                        contents = new String[list.size()][ExportConstant.other_title.length];
                    }
                }
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).get("park_name") == null ? "" : list.get(i).get("park_name").toString();
                    contents[i][2] = list.get(i).get("owner_name") == null ? "" : list.get(i).get("owner_name").toString();
                    contents[i][3] = list.get(i).get("tel_no") == null ? "" : list.get(i).get("tel_no").toString();
                    contents[i][4] = list.get(i).get("car_id") == null ? "" : list.get(i).get("car_id").toString();
                    contents[i][5] = list.get(i).get("role_name") == null ? "" : list.get(i).get("role_name").toString();

                    if (request.getType() == 4) {
                        String expire_time = list.get(i).get("expire_time") == null ? "" : list.get(i).get("expire_time").toString();
                        contents[i][6] = expire_time.substring(0, 4) + "-" + expire_time.substring(4, 6) + "-" + expire_time.substring(6, 8);

                    } else {
                        String start_date = list.get(i).get("start_date") == null ? "" : list.get(i).get("start_date").toString();
                        String end_date = list.get(i).get("end_date") == null ? "" : list.get(i).get("end_date").toString();
                        contents[i][6] = "";
                        if (StringUtils.isNotBlank(start_date) && start_date.length() >= 8) {
                            contents[i][6] = start_date.substring(0, 4) + "-" + start_date.substring(4, 6) + "-" + start_date.substring(6, 8);
                        }
                        contents[i][7] = "";
                        if (StringUtils.isNotBlank(end_date) && end_date.length() >= 8) {
                            contents[i][7] = end_date.substring(0, 4) + "-" + end_date.substring(4, 6) + "-" + end_date.substring(6, 8);
                        }
                        String state = list.get(i).get("state") == null ? "" : list.get(i).get("state").toString();
                        String stateName = "";
                        switch (state) {
                            case "1":
                                stateName = "正常";
                                break;
                            case "2":
                                stateName = "过期";
                                break;
                            case "3":
                                stateName = "暂停";
                                break;
                            default:
                                stateName = "";
                                break;
                        }
                        contents[i][8] = stateName;

                        if (memberGroup.getType() == 1 && isFullFree == 1) {
                            contents[i][9] = list.get(i).get("accumulate_amt") == null ? "" : list.get(i).get("accumulate_amt").toString();
                        }
                    }


                }
            }
            try {
                ExcelUtil.export(exportDto.getFilename(), "sheet1", title, contents, response);
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }


    @Override
    public void getVisitApply(ExportDto exportDto, HttpServletResponse response) {
        TParkVisitHistoryRequest request = JSON.parseObject(exportDto.getJson(), TParkVisitHistoryRequest.class);
        String parkCode = request.getParkCode();
        if (StringUtil.isBlank(parkCode)) {
            throw new CustomException(ExceptionConst.PARAM_PARK_CODE_NOT_NULL);
        }
        ResultJson resultJson = itParkVisitHistoryService.queryVisiterReocrd(request);
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<VisistRecordResponse> list = ((Page) resultJson.getData()).getRecords();
            if (list.size() > 0) {
                contents = new String[list.size()][ExportConstant.visit_apply_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).getVisitor();
                    contents[i][2] = list.get(i).getCarId();
                    contents[i][3] = list.get(i).getMobile();
                    String carType = String.valueOf(list.get(i).getCarType() == null ? "" : (int) list.get(i).getCarType());
                    contents[i][4] = getCarTypeStr(carType);
                    String applyTimeStr = list.get(i).getApplyTime() == null ? "" : list.get(i).getApplyTime().toString();
                    if (StringUtils.isNotBlank(applyTimeStr) && applyTimeStr.length() == 14) {
                        try {
                            applyTimeStr = getTimeString(YYYY_MM_DD_HH_MM_SS, DateUtils.parseDate(applyTimeStr, YYYYMMDDHHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][5] = applyTimeStr;

                    String customerVisitTimeStr = list.get(i).getCustomerVisitTime() == null ? "" : list.get(i).getCustomerVisitTime().toString();
                    if (StringUtils.isNotBlank(customerVisitTimeStr) && customerVisitTimeStr.length() == 14) {
                        try {
                            customerVisitTimeStr = getTimeString(YYYY_MM_DD_HH_MM_SS, DateUtils.parseDate(customerVisitTimeStr, YYYYMMDDHHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][6] = customerVisitTimeStr;

                    contents[i][7] = list.get(i).getApplyName();

                    Byte applyStatus = list.get(i).getApplyStatus();
                    String applyStatusName = "";
                    if (request.getVisitState().equals("2")) {
                        applyStatusName = "已离开";
                    } else {
                        if (applyStatus == 1) {
                            applyStatusName = "待审核";
                        } else if (applyStatus == 2) {
                            applyStatusName = "已通过";
                        } else if (applyStatus == 3) {
                            applyStatusName = "已拒绝";
                        }
                    }


                    contents[i][8] = applyStatusName;
                }
            }
        }

        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.visit_apply_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Override
    public void getVisitJurisdiction(ExportDto exportDto, HttpServletResponse response) {
        TParkVisitHistoryRequest request = JSON.parseObject(exportDto.getJson(), TParkVisitHistoryRequest.class);
        ResultJson resultJson = itParkVisitApplyService.visiterApplyRecord(request);
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<TParkVisitApply> list = ((Page) resultJson.getData()).getRecords();
            if (list.size() > 0) {
                contents = new String[list.size()][ExportConstant.visit_jurisdiction_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).getApplyName();
                    contents[i][2] = list.get(i).getApplyMobile();
                    String applyTimeStr = String.valueOf(list.get(i).getApplyTime());
                    if (StringUtils.isNotBlank(applyTimeStr) && applyTimeStr.length() == 14) {
                        try {
                            applyTimeStr = getTimeString(YYYY_MM_DD_HH_MM_SS, DateUtils.parseDate(applyTimeStr, YYYYMMDDHHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][3] = applyTimeStr;
                    int applyStatus = list.get(i).getApplyStatus();
                    String applyStatusName = "";
                    if (applyStatus == 1) {
                        applyStatusName = "待审核";
                    } else if (applyStatus == 2) {
                        applyStatusName = "已通过";
                    } else if (applyStatus == 3) {
                        applyStatusName = "未通过";
                    }
                    contents[i][4] = applyStatusName;
                }
            }
        }

        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.visit_jurisdiction_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Override
    public void getPaymentQuery(ExportDto exportDto, HttpServletResponse response) {
        MemPaymentRecordDto memPaymentRecordDto = JSON.parseObject(exportDto.getJson(), MemPaymentRecordDto.class);
        ResultJson resultJson = memPaymentRecordService.baseRecords(memPaymentRecordDto);
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<MemPaymentResponse> list = ((Page) (((Map<String, Object>) resultJson.getData()).get("page"))).getRecords();
            if (list.size() > 0) {
                contents = new String[list.size()][ExportConstant.payment_query_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).getCarId();//车牌号
                    contents[i][2] = list.get(i).getOwnerName(); //车主
                    contents[i][3] = list.get(i).getRoleName(); //收费标准
                    contents[i][4] = list.get(i).getAmountNum().toString();//交费金额
                    String upTime = list.get(i).getUpTime();//发生时间
                    if (StringUtils.isNotBlank(upTime) && upTime.length() == 14) {
                        try {
                            upTime = getTimeString(YYYY_MM_DD_HH_MM_SS, DateUtils.parseDate(upTime, YYYYMMDDHHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][5] = upTime;

                    String startDate = list.get(i).getStartDate();//初始到期时间
                    if (StringUtils.isNotBlank(startDate) && startDate.length() == 8) {
                        try {
                            startDate = getTimeString(YYYY_MM_DD, DateUtils.parseDate(startDate, YYYYMMDD));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][6] = startDate;

                    String endDate = list.get(i).getEndDate();//变更到期时间
                    if (StringUtils.isNotBlank(endDate) && endDate.length() == 8) {
                        try {
                            endDate = getTimeString(YYYY_MM_DD, DateUtils.parseDate(endDate, YYYYMMDD));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][7] = endDate;
                }
            }
        }

        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.payment_query_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Override
    public void getTollShift(ExportDto exportDto, HttpServletResponse response) {
        Map<String, Object> param = JSON.parseObject(exportDto.getJson(), Map.class);
        ResultJson resultJson = tollReportController.tollAutomated(param);
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<TollReportResponse> list = ((Page) resultJson.getData()).getRecords();
            if (!CollectionUtils.isEmpty(list)) {
                contents = new String[list.size()][ExportConstant.toll_shift_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).getTollNo();
                    contents[i][2] = list.get(i).getTollName();
                    String onduty = String.valueOf(list.get(i).getOnDuty());
                    String offduty = String.valueOf(list.get(i).getOffDuty());
                    try {
                        contents[i][3] = onduty.substring(0, 4) + "-" + onduty.substring(4, 6) + "-" + onduty.substring(6, 8) + " " + onduty.substring(8, 10) + ":" + onduty.substring(10, 12) + ":" + onduty.substring(12, 14);
                    } catch (Exception e) {
                        contents[i][3] = "-";
                    }
                    try {
                        contents[i][4] = offduty.substring(0, 4) + "-" + offduty.substring(4, 6) + "-" + offduty.substring(6, 8) + " " + offduty.substring(8, 10) + ":" + offduty.substring(10, 12) + ":" + offduty.substring(12, 14);
                    } catch (Exception e) {
                        contents[i][4] = "-";
                    }
                    contents[i][5] = list.get(i).getWorkTime();
                    contents[i][6] = String.valueOf(list.get(i).getChargeAmount());
                    contents[i][7] = String.valueOf(list.get(i).getCouponAmount());
                    contents[i][8] = String.valueOf(list.get(i).getMemCardAmount());
                    contents[i][9] = String.valueOf(list.get(i).getTotal());
                }
            }
        }

        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.toll_shift_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void getDayStatistics(ExportDto exportDto, HttpServletResponse response) {
        Map<String, Object> param = JSON.parseObject(exportDto.getJson(), Map.class);
        log.info("导出日统计报表:c接口请求参数,param======>" + param);
        List<Map<String, String>> list = kesbApiService.queryKesbApiList(Constant.C_Daily_STATISTICS, param);
        log.info("导出日统计报表:c接口返回参数,param======>" + list);
        String[][] contents = null;
        if (!CollectionUtils.isEmpty(list)) {
            contents = new String[list.size()][ExportConstant.day_statistics_title.length];
            for (int i = 0; i < list.size(); i++) {
                contents[i][0] = i + 1 + "";
                contents[i][1] = list.get(i).get("tollname");//收费员姓名
                contents[i][2] = list.get(i).get("total_actual_account"); //收费总额
                contents[i][3] = String.valueOf(Double.valueOf(list.get(i).get("sumCouponMoney")) +
                        Double.valueOf(list.get(i).get("sumCouponFree"))
                        + Double.valueOf(list.get(i).get("sumCouponMOney_time"))
                        + Double.valueOf(list.get(i).get("freepay_guest_amt"))
                        + Double.valueOf(list.get(i).get("noclear_ec_amt"))); //优惠券
                contents[i][4] = list.get(i).get("sumMember_account");//长租车
                contents[i][5] = String.valueOf(Double.valueOf(list.get(i).get("total_actual_account")) +
                        Double.valueOf(list.get(i).get("sumCouponMoney")) + Double.valueOf(list.get(i).get("sumCouponFree")) +
                        Double.valueOf(list.get(i).get("sumCouponMOney_time")) +
                        Double.valueOf(list.get(i).get("freepay_guest_amt")) +
                        Double.valueOf(list.get(i).get("noclear_ec_amt")) +
                        Double.valueOf(list.get(i).get("sumMember_account"))); //合计
            }
        }
        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.day_statistics_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void getAttendanceStatistics(ExportDto exportDto, HttpServletResponse response) {
        ParkWorkLogRequset parkWorkLogRequset = JSON.parseObject(exportDto.getJson(), ParkWorkLogRequset.class);
        Page<TParkWorkLogResponse> page = itParkWorkLogService.queryNew(parkWorkLogRequset);
        String[][] contents = null;
        if (page != null) {
            List<TParkWorkLogResponse> list = page.getRecords();
            if (!CollectionUtils.isEmpty(list)) {
                contents = new String[list.size()][ExportConstant.attendance_statistics_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).getParkName();//停车场名称
                    contents[i][2] = list.get(i).getName(); //姓名
                    contents[i][3] = list.get(i).getLoginname(); //账号
                    contents[i][4] = list.get(i).getTollType();//角色名称

                    String onDuty = list.get(i).getOnDuty();
                    if (StringUtils.isNotBlank(onDuty) && onDuty.length() == 14) {
                        try {
                            onDuty = getTimeString(YYYY_MM_DD_HH_MM_SS, DateUtils.parseDate(onDuty, YYYYMMDDHHMMSS));
                            contents[i][5] = onDuty.substring(0, 10);
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    } else {
                        contents[i][5] = onDuty; //出勤日期
                    }

                    String beginTime = list.get(i).getBeginTime();
                    if (StringUtils.isNotBlank(beginTime) && beginTime.length() == 7) {
                        try {
                            beginTime = beginTime.substring(1);
                            beginTime = getTimeString(HH_MM_SS, DateUtils.parseDate(beginTime, HHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][6] = beginTime; //上班时间

                    String endTime = list.get(i).getEndTime();//下班时间
                    if (StringUtils.isNotBlank(endTime) && endTime.length() == 7) {
                        try {
                            endTime = endTime.substring(1);
                            endTime = getTimeString(HH_MM_SS, DateUtils.parseDate(endTime, HHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][7] = endTime;

                    String qiandaoTime = "";
                    onDuty = list.get(i).getOnDuty();
                    if (StringUtils.isNotBlank(onDuty) && onDuty.length() == 14) {
                        try {
                            qiandaoTime = onDuty.substring(8);
                            qiandaoTime = getTimeString(HH_MM_SS, DateUtils.parseDate(qiandaoTime, HHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][8] = qiandaoTime;//签到时间


                    String offDuty = list.get(i).getOffDuty();//签出时间
                    if (StringUtils.isNotBlank(offDuty) && offDuty.length() == 14) {
                        try {
                            offDuty = offDuty.substring(8);
                            offDuty = getTimeString(HH_MM_SS, DateUtils.parseDate(offDuty, HHMMSS));
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    }
                    contents[i][9] = offDuty;//签出时间
                    contents[i][10] = list.get(i).getStatusName();//上班状态
                    contents[i][11] = list.get(i).getInsteadMan();//代班人
                }
            }
            try {
                ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.attendance_statistics_title, contents, response);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void getTollAccount(ExportDto exportDto, HttpServletResponse response) {
        Map<String, Object> param = JSON.parseObject(exportDto.getJson(), Map.class);
        log.info("导出收费员账号:c接口请求参数,param======>" + param);
        List<Map<String, String>> list = kesbApiService.queryKesbApiList(Constant.C_TOLL_ACCOUNT_QUERY, param);
        log.info("导出收费员账号:c接口返回参数,param======>" + list);
        String[][] contents = null;
        if (!CollectionUtils.isEmpty(list)) {
            contents = new String[list.size()][ExportConstant.toll_account_title.length];
            for (int i = 0; i < list.size(); i++) {
                contents[i][0] = i + 1 + "";
                contents[i][1] = list.get(i).get("no");//工号
                contents[i][2] = list.get(i).get("name"); //姓名
            }
        }
        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.toll_account_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Override
    public void down(String fileName, HttpServletResponse response) {

        try {
            fileName = java.net.URLDecoder.decode("/model/" + fileName, "utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        InputStream inputStream = this.getClass().getResourceAsStream(fileName);
        try (OutputStream outputStream = response.getOutputStream()) {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.split("/")[2].getBytes("UTF-8"), "iso-8859-1") + "");
            IOUtils.copy(inputStream, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void queryOrder(ExportDto exportDto, HttpServletResponse response) {
        ResultJson resultJson = tParkOrderController.listInfo(JSON.parseObject(exportDto.getJson(), Map.class));
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<TOrderResponse> list = (List<TOrderResponse>) ((PageResponse) ((Map) resultJson.getData()).get("page")).getRecords();
            if (list.size() > 0) {
                contents = new String[list.size()][ExportConstant.order_query_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).getUsingobj();
                    String ordertime = list.get(i).getOrdertime();
                    contents[i][2] = ordertime.substring(0, 4) + "-" + ordertime.substring(4, 6) + "-" + ordertime.substring(6, 8) + " " + ordertime.substring(8, 10) + ":" + ordertime.substring(10, 12) + ":" + ordertime.substring(12, 14);
                    contents[i][3] = list.get(i).getTradetype().equals("18") ? "延时缴费" : "正常缴费";
                    contents[i][4] = casePayType(Integer.valueOf(list.get(i).getPayType()));
                    contents[i][5] = String.valueOf(list.get(i).getTradeamount());
                    contents[i][6] = String.valueOf(list.get(i).getFeeamt());

                }
            }
        }
        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.order_query_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void queryArrearage(ExportDto exportDto, HttpServletResponse response) {
        int type = exportDto.getType();
        String[][] contents = null;
        ResultJson resultJson = new ResultJson();
        List<Map<String, String>> list = new ArrayList<>();
        String[] title = {};
        if (type == 1) {
            title = ExportConstant.order_arrearage_title;
            resultJson = tParkOrderController.arrearageList(exportDto.getJson());
            if (resultJson.getCode() == 200) {
                list = (List) resultJson.getData();
                if (list.size() > 0) {
                    contents = new String[list.size()][title.length];
                    for (int i = 0; i < list.size(); i++) {
                        contents[i][0] = i + 1 + "";
                        contents[i][1] = list.get(i).get("car_id");
                        contents[i][2] = list.get(i).get("toll_name");
                        contents[i][3] = list.get(i).get("region_name");
                        String intime = list.get(i).get("intime");
                        String outtime = list.get(i).get("outtime");
                        contents[i][4] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                        contents[i][5] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                        contents[i][6] = list.get(i).get("parktime");
                        contents[i][7] = list.get(i).get("parkamt");
                        contents[i][8] = list.get(i).get("chargeamt");

                    }
                }
            }
        } else if (type == 2) {
            title = ExportConstant.order_arrearage_pay_title;
            resultJson = tParkOrderController.arrearagePay(exportDto.getJson());
            if (resultJson.getCode() == 200) {
                list = (List) resultJson.getData();
                if (list.size() > 0) {
                    contents = new String[list.size()][title.length];
                    for (int i = 0; i < list.size(); i++) {
                        contents[i][0] = i + 1 + "";
                        contents[i][1] = list.get(i).get("car_id");
                        contents[i][2] = list.get(i).get("toll_name");
                        contents[i][3] = list.get(i).get("region_name");
                        String intime = list.get(i).get("intime");
                        String outtime = list.get(i).get("outtime");
                        contents[i][4] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                        contents[i][5] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                        contents[i][6] = list.get(i).get("parktime");
                        contents[i][7] = list.get(i).get("parkamt");
                        contents[i][8] = list.get(i).get("chargeamt");
                        contents[i][9] = list.get(i).get("uploadtime");
                        contents[i][10] = list.get(i).get("paystate").equals("1") ? "已补缴" : "未缴费";

                    }
                }
            }
        } else {
            title = ExportConstant.order_badDebt_title;
            resultJson = tParkOrderController.queryBadDebt(exportDto.getJson());
            if (resultJson.getCode() == 200) {
                list = (List) resultJson.getData();
                if (list.size() > 0) {
                    contents = new String[list.size()][title.length];
                    for (int i = 0; i < list.size(); i++) {
                        contents[i][0] = i + 1 + "";
                        contents[i][1] = list.get(i).get("car_id");
                        contents[i][2] = list.get(i).get("toll_name");
                        contents[i][3] = list.get(i).get("region_name");
                        String intime = list.get(i).get("intime");
                        String outtime = list.get(i).get("outtime");
                        contents[i][4] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                        contents[i][5] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                        contents[i][6] = list.get(i).get("parktime");
                        contents[i][7] = list.get(i).get("parkamt");
                        contents[i][8] = list.get(i).get("baddebt_time");
                        contents[i][9] = list.get(i).get("remark");

                    }
                }
            }
        }
        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void queryInvoiceStatistics(ExportDto exportDto, HttpServletResponse response) {
        int type = exportDto.getType();
        String[][] contents = null;
        ResultJson resultJson = new ResultJson();
        List<InvoiceDTO> list = new ArrayList<>();
        String[] title = {};
        if (type == 1) {
            title = ExportConstant.financia_statistics_title;
            String jsons = exportDto.getJson();
            InvoiceRequest req = JSON.parseObject(jsons, InvoiceRequest.class);
            resultJson = financialManageController.queryYun1(req);
            if (resultJson.getCode() == 200) {
                list = (List<InvoiceDTO>) ((PageResponse) resultJson.getData()).getRecords();
                if (list.size() > 0) {
                    contents = new String[list.size()][title.length];
                    for (int i = 0; i < list.size(); i++) {
                        contents[i][0] = i + 1 + "";
                        contents[i][1] = list.get(i).getInvoiceType().equals("1") ? "红票" : "蓝票";
                        contents[i][2] = list.get(i).getBuyerName();
                        contents[i][3] = list.get(i).getInvoicePrice();
                        String applyDate = list.get(i).getApplyDate();
                        contents[i][4] = applyDate.substring(0, 4) + "-" + applyDate.substring(4, 6) + "-" + applyDate.substring(6, 8) + " " + applyDate.substring(8, 10) + ":" + applyDate.substring(10, 12) + ":" + applyDate.substring(12, 14);
                        contents[i][5] = list.get(i).getBuyerBankAccount();
                        contents[i][6] = list.get(i).getBuyerPhone();
                        contents[i][7] = "开票成功";
                        String invoiceDate = list.get(i).getInvoiceDate();
                        contents[i][8] = invoiceDate.substring(0, 4) + "-" + invoiceDate.substring(4, 6) + "-" + invoiceDate.substring(6, 8) + " " + invoiceDate.substring(8, 10) + ":" + invoiceDate.substring(10, 12) + ":" + invoiceDate.substring(12, 14);
                    }
                }
            }
        }
        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void queryFinancialStatements(ExportDto exportDto, HttpServletResponse response) {
        Map<String, Object> reqMap = JSON.parseObject(exportDto.getJson(), Map.class);
        String querymode = reqMap.get("querymode").toString();//2 日汇总 3 月汇总
        String[] title = new String[0];
        String sheet = "sheet1";
        if (querymode.equals("3")) {
            sheet = "月汇总";
            title = ExportConstant.financia_title_month;
        } else {
            sheet = "日汇总";
            title = ExportConstant.financia_title_day;
        }

        String[][] contents = null;

        QueryWrapper<TParkInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("park_code", reqMap.get("park_code"));
        TParkInfo one = itParkInfoService.getOne(queryWrapper);
        ResultJson resultJson = financialManageController.parkBill(reqMap);
        if (resultJson.getCode() == 200) {
            List<Map<String, String>> list = (List<Map<String, String>>) resultJson.getData();
            //2 日汇总
            if (querymode.equals("2") && list.size() > 0) {
                contents = new String[list.size()][title.length];
                for (int i = 0; i < list.size(); i++) {
                    Map<String, String> map = list.get(i);
                    FinancialResponse e = JSON.parseObject(JSON.toJSONString(map), FinancialResponse.class);

                    //停车场
                    contents[i][0] = one.getName();
                    //日期
                    Long occur_date = e.getOccur_date();
                    if (String.valueOf(occur_date).length() == 6) {
                        contents[i][1] = String.valueOf(occur_date).substring(0, 4) + "-" + String.valueOf(occur_date).substring(4, 6);
                    } else if (String.valueOf(occur_date).length() == 8) {
                        contents[i][1] = String.valueOf(occur_date).substring(0, 4) + "-" + String.valueOf(occur_date).substring(4, 6) + "-" + String.valueOf(occur_date).substring(6, 8);
                    } else {
                        contents[i][1] = "-";
                    }
                    if (e.getOccur_date() < 20180201L) {
                        //临停总额
                        contents[i][2] = "+" + String.valueOf(addBigDecimal(e.getCitycard_actual_account(),
                                e.getSelf_weixin_account(),
                                e.getSelf_zhifubao_account(),
                                e.getBalance_actual_account(),
                                e.getYu_epay_amt(),
                                subtractBigDecimal(e.getOwner_pay_time_relate_account(), e.getAuto_pay_time_relate_account())));
                        //临停笔数
                        contents[i][3] = String.valueOf(addLong(e.getCharge_amt_cnt(), e.getCitycard_total_cnt(), e.getYu_epay_cnt(), e.getSelf_total_cnt(), e.getBalance_total_cnt()));
                        //电子支付总额
                        contents[i][4] = "+" + String.valueOf(addBigDecimal(e.getCitycard_actual_account(),
                                e.getSelf_weixin_account(),
                                e.getSelf_zhifubao_account(),
                                e.getBalance_actual_account(),
                                e.getYu_epay_amt(),
                                subtractBigDecimal(e.getOwner_pay_time_relate_account(), e.getAuto_pay_time_relate_account())));
                        //电子支付笔数
                        contents[i][5] = String.valueOf(addLong(e.getCitycard_total_cnt(), e.getYu_epay_cnt(), e.getSelf_total_cnt(), e.getBalance_total_cnt()));
                        //电子券总额
                        contents[i][18] = "+" + String.valueOf(e.getElec_coupon_amt());
                        // 电子券笔数
                        contents[i][19] = String.valueOf(e.getElec_coupon_cnt());
                        //结算总额
                        contents[i][20] = "+" + String.valueOf(e.getElec_coupon_amt().subtract(e.getNoclear_ec_amt()).compareTo(BigDecimal.ZERO) == 1 ? e.getElec_coupon_amt().subtract(e.getNoclear_ec_amt()) : e.getNoclear_ec_amt().subtract(e.getElec_coupon_amt()));
                        // 结算笔数
                        contents[i][21] = String.valueOf(e.getElec_coupon_cnt() - e.getNoclear_ec_count() > 0 ? e.getElec_coupon_cnt() - e.getNoclear_ec_count() : 0);
                        //不结算总额
                        contents[i][22] = "+" + String.valueOf(e.getNoclear_ec_amt());
                        // 不结算笔数
                        contents[i][23] = String.valueOf(e.getNoclear_ec_count());
                        //异常流水实收总额
                        // 异常流水实收笔数


                    } else {
                        contents[i][2] = "+" + String.valueOf(addBigDecimal(e.getNew_actual_amt(), e.getNew_ex_actual_amt(), e.getClear_amt(), e.getNew_offline_amt(), e.getNew_elec_amt(), e.getNew_freepay_amt()));
                        contents[i][3] = String.valueOf(addLong(e.getNew_ex_actual_cnt(), e.getNew_normal_cnt()));
                        contents[i][4] = "+" + String.valueOf(e.getClear_amt());
                        contents[i][5] = String.valueOf(addLong(e.getClear_cnt(), e.getNew_inner_card_cnt()));
                        contents[i][18] = "+" + String.valueOf(addBigDecimal(e.getNew_elec_amt(), e.getNew_freepay_amt()));
                        contents[i][19] = String.valueOf(addLong(e.getNew_elec_cnt(), e.getNew_elec_cnt(), e.getNew_freepay_cnt(), e.getNew_freepay_cnt()));
                        contents[i][20] = "+" + String.valueOf(e.getNew_clear_elec_amt());
                        contents[i][21] = String.valueOf(e.getNew_clear_elec_cnt());
                        if (addBigDecimal(e.getNew_freepay_amt(), subtractBigDecimal(e.getNew_clear_elec_amt()), e.getNew_elec_amt()).compareTo(new BigDecimal("0")) == 1) {
                            contents[i][22] = "+" + String.valueOf(addBigDecimal(e.getNew_freepay_amt(), subtractBigDecimal(e.getNew_clear_elec_amt()), e.getNew_elec_amt()));
                        } else {
                            contents[i][22] = "+" + "0.00";
                        }
                        if ((e.getNew_elec_cnt() + e.getNew_freepay_cnt() - e.getNew_clear_elec_cnt()) < 0) {
                            contents[i][23] = "0";
                        } else {
                            contents[i][23] = String.valueOf(e.getNew_elec_cnt() + e.getNew_freepay_cnt() - e.getNew_clear_elec_cnt());
                        }
                    }
                    //套餐收入总额
                    contents[i][6] = "+" + String.valueOf(e.getSumMember_account());
                    //套餐收入笔数
                    contents[i][7] = String.valueOf(e.getSumMember_cnt());
                    //支出总额
                    contents[i][8] = "-" + String.valueOf(e.getTransamt());
                    //支出笔数
                    contents[i][9] = String.valueOf(e.getTrans_cnt());
                    //期初可提现余额
                    contents[i][10] = String.valueOf(e.getLastbalance());
                    //期末可提现余额
                    contents[i][11] = String.valueOf(e.getEndbalance());
                    //支付宝总额
                    contents[i][12] = "+" + String.valueOf(e.getSelf_zhifubao_account());
                    //支付宝笔数  差异self_zfb_count
                    contents[i][13] = String.valueOf(e.getSelf_total_cnt());
                    //微信总额
                    contents[i][14] = "+" + String.valueOf(e.getSelf_weixin_account());
                    // 微信笔数  差异elf_weixin_count
                    contents[i][15] = String.valueOf(e.getSelf_total_cnt());
                    // ETC总额
                    contents[i][16] = "+" + String.valueOf(e.getBalance_actual_account());
                    // ETC笔数
                    contents[i][17] = String.valueOf(e.getBalance_total_cnt());

                    //异常流水实收总额  差异 <20180201:e.getNew_ex_actual_amt()   >=20180201:addBigDecimal(e.getNew_ex_actual_amt(),e.getNew_ex_epay_amt())
                    contents[i][24] = "+" + String.valueOf(e.getNew_ex_epay_cnt());
                    // 异常流水实收笔数
                    contents[i][25] = String.valueOf(e.getNew_ex_actual_cnt());
                    //冲正总额
                    contents[i][26] = "+" + String.valueOf(e.getReveral_amt());
                    //冲正笔数
                    contents[i][27] = String.valueOf(e.getReveral_cnt());
                    //提现总额
                    contents[i][28] = "-" + String.valueOf(e.getTransamt());
                    // 提现笔数
                    contents[i][29] = String.valueOf(e.getTrans_cnt());
                    //微信/支付宝手续费总额
                    contents[i][30] = "-" + String.valueOf(addBigDecimal(e.getFeeamt(), e.getReveral_fee_amt()));
                    if (e.getReveral_cnt() > 0) {
                        //微信/支付宝手续费笔数
                        contents[i][31] = String.valueOf(addLong(e.getFeeCnt(), e.getReveral_cnt()));
                    } else {
                        contents[i][31] = String.valueOf(e.getFeeCnt());
                    }


                }
            }

            if (querymode.equals("3") && list.size() > 0) {
                contents = new String[list.size()][title.length];
                for (int i = 0; i < list.size(); i++) {
                    Map<String, String> map = list.get(i);
                    FinancialResponse e = JSON.parseObject(JSON.toJSONString(map), FinancialResponse.class);

                    //停车场
                    contents[i][0] = one.getName();
                    //日期
                    Long occur_date = e.getOccur_date();
                    if (String.valueOf(occur_date).length() == 6) {
                        contents[i][1] = String.valueOf(occur_date).substring(0, 4) + "-" + String.valueOf(occur_date).substring(4, 6);
                    } else {
                        contents[i][1] = "-";
                    }
                    if (e.getOccur_date() < 20180201L) {
                        //临停总额
                        contents[i][2] = "+" + String.valueOf(addBigDecimal(e.getTotal_actual_account(), e.getCitycard_actual_account(),
                                e.getSelf_weixin_account(),
                                e.getSelf_zhifubao_account(),
                                e.getBalance_actual_account(),
                                e.getYu_epay_amt()).subtract(e.getOwner_pay_time_relate_account()).subtract(e.getAuto_pay_time_relate_account())
                                .add(e.getElec_coupon_amt()).add(e.getOffline_coupon_amt()));
                        //临停笔数
                        contents[i][3] = String.valueOf(addLong(e.getCharge_amt_cnt(), e.getCitycard_total_cnt(), e.getYu_epay_cnt(), e.getSelf_total_cnt(), e.getBalance_total_cnt()));
                        //电子支付总额
                        contents[i][4] = "+" + String.valueOf(addBigDecimal(e.getCitycard_actual_account(),
                                e.getSelf_weixin_account(),
                                e.getSelf_zhifubao_account(),
                                e.getBalance_actual_account(),
                                e.getYu_epay_amt(),
                                subtractBigDecimal(e.getOwner_pay_time_relate_account(), e.getAuto_pay_time_relate_account())));
                        //电子支付笔数
                        contents[i][5] = String.valueOf(addLong(e.getCitycard_total_cnt(), e.getYu_epay_cnt(), e.getSelf_total_cnt(), e.getBalance_total_cnt()));
                        //电子券总额
                        contents[i][12] = "+" + String.valueOf(e.getElec_coupon_amt());
                        // 电子券笔数
                        contents[i][13] = String.valueOf(e.getElec_coupon_cnt());
                        //结算总额
                        contents[i][14] = "+" + String.valueOf(e.getElec_coupon_amt().subtract(e.getNoclear_ec_amt()).compareTo(BigDecimal.ZERO) == 1 ? e.getElec_coupon_amt().subtract(e.getNoclear_ec_amt()) : e.getNoclear_ec_amt().subtract(e.getElec_coupon_amt()));
                        // 结算笔数
                        contents[i][15] = String.valueOf(e.getElec_coupon_cnt() - e.getNoclear_ec_count() > 0 ? e.getElec_coupon_cnt() - e.getNoclear_ec_count() : 0);
                        //不结算总额
                        contents[i][16] = "+" + String.valueOf(e.getNoclear_ec_amt());
                        // 不结算笔数
                        contents[i][17] = String.valueOf(e.getNoclear_ec_count());

                    } else {
                        contents[i][2] = "+" + String.valueOf(addBigDecimal(e.getNew_actual_amt(), e.getNew_ex_actual_amt(), e.getClear_amt(), e.getNew_offline_amt(), e.getNew_elec_amt(), e.getNew_freepay_amt()));
                        contents[i][3] = String.valueOf(addLong(e.getNew_ex_actual_cnt(), e.getNew_normal_cnt()));
                        contents[i][4] = "+" + String.valueOf(e.getClear_amt());
                        contents[i][5] = String.valueOf(addLong(e.getClear_cnt(), e.getNew_inner_card_cnt()));
                        contents[i][12] = "+" + String.valueOf(addBigDecimal(e.getNew_elec_amt(), e.getNew_freepay_amt()));
                        contents[i][13] = String.valueOf(addLong(e.getNew_elec_cnt(), e.getNew_elec_cnt(), e.getNew_freepay_cnt(), e.getNew_freepay_cnt()));
                        contents[i][14] = "+" + String.valueOf(e.getNew_clear_elec_amt());
                        contents[i][15] = String.valueOf(e.getNew_clear_elec_cnt());
                        if (addBigDecimal(e.getNew_freepay_amt(), subtractBigDecimal(e.getNew_clear_elec_amt()), e.getNew_elec_amt()).compareTo(new BigDecimal("0")) == 1) {
                            contents[i][16] = "+" + String.valueOf(addBigDecimal(e.getNew_freepay_amt(), subtractBigDecimal(e.getNew_clear_elec_amt()), e.getNew_elec_amt()));
                        } else {
                            contents[i][16] = "+" + "0.00";
                        }
                        if ((e.getNew_elec_cnt() + e.getNew_freepay_cnt() - e.getNew_clear_elec_cnt()) < 0) {
                            contents[i][17] = "0";
                        } else {
                            contents[i][17] = String.valueOf(e.getNew_elec_cnt() + e.getNew_freepay_cnt() - e.getNew_clear_elec_cnt());
                        }
                    }
                    //套餐收入总额
                    contents[i][6] = "+" + String.valueOf(e.getSumMember_account());
                    //套餐收入笔数
                    contents[i][7] = String.valueOf(e.getSumMember_cnt());
                    //支出总额
                    contents[i][8] = "-" + String.valueOf(e.getTransamt());
                    //支出笔数
                    contents[i][9] = String.valueOf(e.getTrans_cnt());
                    //期初可提现余额
                    contents[i][10] = String.valueOf(e.getLastbalance());
                    //期末可提现余额
                    contents[i][11] = String.valueOf(e.getEndbalance());

                    //异常流水实收总额  差异 <20180201:e.getNew_ex_actual_amt()   >=20180201:addBigDecimal(e.getNew_ex_actual_amt(),e.getNew_ex_epay_amt())
                    contents[i][18] = "+" + String.valueOf(e.getNew_ex_epay_amt());
                    // 异常流水实收笔数
                    contents[i][19] = String.valueOf(e.getNew_ex_epay_cnt());
                    //冲正总额
                    contents[i][20] = "+" + String.valueOf(e.getReveral_amt());
                    //冲正笔数
                    contents[i][21] = String.valueOf(e.getReveral_cnt());
                    //提现总额
                    contents[i][22] = "-" + String.valueOf(e.getTransamt());
                    // 提现笔数
                    contents[i][23] = String.valueOf(e.getTrans_cnt());
                    //微信/支付宝手续费总额
                    contents[i][24] = "-" + String.valueOf(addBigDecimal(e.getFeeamt(), e.getReveral_fee_amt()));
                    if (e.getReveral_cnt() > 0) {
                        //微信/支付宝手续费笔数
                        contents[i][25] = String.valueOf(addLong(e.getFeeCnt(), e.getReveral_cnt()));
                    } else {
                        contents[i][25] = String.valueOf(e.getFeeCnt());
                    }


                }
            }
        }

        try {
            ExcelUtil.export(exportDto.getFilename(), sheet, title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 累加金额
     *
     * @param aBigDecimal
     * @return
     */
    private static BigDecimal addBigDecimal(BigDecimal... aBigDecimal) {
        BigDecimal toal = new BigDecimal(0);
        for (BigDecimal a : aBigDecimal) {
            if (org.springframework.util.StringUtils.isEmpty(a)) {
                a = new BigDecimal(0);
            }
            toal = toal.add(a);
        }
        return toal;
    }

    /**
     * 累减金额
     **/
    private static BigDecimal subtractBigDecimal(BigDecimal... aBigDecimal) {
        BigDecimal toal = new BigDecimal(0);
        for (BigDecimal a : aBigDecimal) {
            if (org.springframework.util.StringUtils.isEmpty(a)) {
                a = new BigDecimal(0);
            }
            toal = toal.subtract(a);
        }
        return toal;
    }

    /**
     * 累加次数
     **/
    private static long addLong(Long... aLong) {
        Long toal = new Long(0);
        for (Long a : aLong) {
            if (org.springframework.util.StringUtils.isEmpty(a)) {
                a = new Long(0);
            }
            toal = a + toal;
        }
        return toal;
    }

    @Override
    public void queryAbNormal(ExportDto exportDto, HttpServletResponse response) {
        ResultJson resultJson = tParkOrderController.abNormalList(exportDto.getJson());
        String[][] contents = null;
        if (resultJson.getCode() == 200) {
            List<Map<String, String>> list = (List) resultJson.getData();
            if (list.size() > 0) {
                contents = new String[list.size()][ExportConstant.order_abNormal_title.length];
                for (int i = 0; i < list.size(); i++) {
                    contents[i][0] = i + 1 + "";
                    contents[i][1] = list.get(i).get("car_id");
                    String intime = list.get(i).get("intime");
                    String outtime = list.get(i).get("outtime");
                    contents[i][2] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                    contents[i][3] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                    contents[i][4] = list.get(i).get("parkamt");
                    contents[i][5] = list.get(i).get("chargeamt");
                    contents[i][6] = caseExceptType(Integer.valueOf(list.get(i).get("except_type")));
                    contents[i][7] = list.get(i).get("deal_status").equals("0") ? "未处理" : "已处理";
                }
            }
        }
        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", ExportConstant.order_abNormal_title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void queryRecord(ExportDto exportDto, HttpServletResponse response) {
        Map request = JSON.parseObject(exportDto.getJson(), Map.class);
        int type = exportDto.getType();
        ResultJson resultJson = new ResultJson();
        String[] title = {};
        String[][] contents = null;
        if (type == 7) {
            resultJson = tParkOrderController.getParkingRecord(request);
        } else {
            resultJson = tParkOrderController.parkRecords(request);
        }

        switch (type) {
            case 1:
                title = ExportConstant.order_record_title_one;
                if (resultJson.getCode() == 200) {
                    List<Map<String, Object>> list = ((Page) resultJson.getData()).getRecords();
                    if (list.size() > 0) {
                        contents = new String[list.size()][title.length];
                        for (int i = 0; i < list.size(); i++) {
                            contents[i][0] = i + 1 + "";
                            contents[i][1] = list.get(i).get("car_id").toString();
                            String intime = String.valueOf(list.get(i).get("intime"));
                            String outtime = String.valueOf(list.get(i).get("outtime"));
                            contents[i][2] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                            contents[i][3] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                            contents[i][4] = paseMin(Long.valueOf(String.valueOf(list.get(i).get("parktime"))));
                            contents[i][5] = String.valueOf(list.get(i).get("chargeamt"));
                            contents[i][6] = casePayFlag(list.get(i));

                            QueryWrapper<TDictionary> qw = new QueryWrapper<>();
                            qw.eq("category_en", "outoperate");
                            qw.eq("cval", list.get(i).get("outoperate"));
                            String outoperate = "";
                            try {
                                outoperate = itDictionaryService.list(qw).get(0).getItem();
                            } catch (Exception e) {
                                outoperate = "-";
                            }
                            contents[i][7] = outoperate;
                        }
                    }
                }
                break;

            case 4:
                title = ExportConstant.order_record_title_four;
                if (resultJson.getCode() == 200) {
                    List<Map<String, Object>> list = ((Page) resultJson.getData()).getRecords();
                    if (list.size() > 0) {
                        contents = new String[list.size()][title.length];
                        for (int i = 0; i < list.size(); i++) {
                            contents[i][0] = i + 1 + "";
                            contents[i][1] = list.get(i).get("car_id").toString();
                            String intime = String.valueOf(list.get(i).get("intime"));
                            String outtime = String.valueOf(list.get(i).get("outtime"));
                            contents[i][2] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                            contents[i][3] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                            contents[i][4] = paseMin(Long.valueOf(String.valueOf(list.get(i).get("parktime"))));
                            contents[i][5] = String.valueOf(list.get(i).get("chargeamt"));
                            contents[i][6] = String.valueOf(list.get(i).get("countFee"));
                            contents[i][7] = casePayFlag(list.get(i));
                            QueryWrapper<TDictionary> qw = new QueryWrapper<>();
                            qw.eq("category_en", "outoperate");
                            qw.eq("cval", list.get(i).get("outoperate"));
                            String outoperate = "";
                            try {
                                outoperate = itDictionaryService.list(qw).get(0).getItem();
                            } catch (Exception e) {
                                outoperate = "-";
                            }
                            contents[i][7] = outoperate;
                        }
                    }
                }
                break;
            case 6:
                title = ExportConstant.order_record_title_six;
                if (resultJson.getCode() == 200) {
                    List<Map<String, Object>> list = ((Page) resultJson.getData()).getRecords();
                    if (list.size() > 0) {
                        contents = new String[list.size()][title.length];
                        for (int i = 0; i < list.size(); i++) {
                            contents[i][0] = i + 1 + "";
                            contents[i][1] = list.get(i).get("car_id").toString();
                            String intime = String.valueOf(list.get(i).get("intime"));
                            String outtime = String.valueOf(list.get(i).get("outtime"));
                            contents[i][2] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                            contents[i][3] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                            contents[i][4] = paseMin(Long.valueOf(String.valueOf(list.get(i).get("parktime"))));
                            contents[i][5] = casePayFlag(list.get(i));
                            QueryWrapper<TDictionary> qw = new QueryWrapper<>();
                            qw.eq("category_en", "outoperate");
                            qw.eq("cval", list.get(i).get("outoperate"));
                            String outoperate = "";
                            try {
                                outoperate = itDictionaryService.list(qw).get(0).getItem();
                            } catch (Exception e) {
                                outoperate = "-";
                            }
                            contents[i][6] = outoperate;
                        }
                    }
                }
                break;
            case 7:
                title = ExportConstant.order_record_title_seven;
                if (resultJson.getCode() == 200) {
                    List<Map<String, Object>> list = ((Page) resultJson.getData()).getRecords();
                    if (list.size() > 0) {
                        contents = new String[list.size()][title.length];
                        for (int i = 0; i < list.size(); i++) {
                            contents[i][0] = i + 1 + "";
                            contents[i][1] = list.get(i).get("car_id").toString();
                            String intime = String.valueOf(list.get(i).get("intime"));
                            String outtime = String.valueOf(list.get(i).get("outtime"));
                            contents[i][2] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);

                            if (outtime.equals("0")) {
                                contents[i][3] = "-";
                            } else {
                                contents[i][3] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                            }
                            String parktime = String.valueOf(list.get(i).get("parktime"));
                            if (parktime.equals("0")) {
                                contents[i][4] = "-";
                            } else {
                                contents[i][4] = paseMin(Long.valueOf(parktime));
                            }

                        }
                    }
                }
                break;
            default:
                title = ExportConstant.order_record_title_two;
                if (resultJson.getCode() == 200) {
                    List<Map<String, Object>> list = ((Page) resultJson.getData()).getRecords();
                    if (list.size() > 0) {
                        contents = new String[list.size()][title.length];
                        for (int i = 0; i < list.size(); i++) {
                            contents[i][0] = i + 1 + "";
                            contents[i][1] = list.get(i).get("car_id").toString();
                            String intime = String.valueOf(list.get(i).get("intime"));
                            String outtime = String.valueOf(list.get(i).get("outtime"));
                            contents[i][2] = intime.substring(0, 4) + "-" + intime.substring(4, 6) + "-" + intime.substring(6, 8) + " " + intime.substring(8, 10) + ":" + intime.substring(10, 12) + ":" + intime.substring(12, 14);
                            contents[i][3] = outtime.substring(0, 4) + "-" + outtime.substring(4, 6) + "-" + outtime.substring(6, 8) + " " + outtime.substring(8, 10) + ":" + outtime.substring(10, 12) + ":" + outtime.substring(12, 14);
                            contents[i][4] = paseMin(Long.valueOf(String.valueOf(list.get(i).get("parktime"))));
                            contents[i][5] = String.valueOf(list.get(i).get("chargeamt"));
                            contents[i][6] = String.valueOf(list.get(i).get("coupon_amt"));
                            contents[i][7] = casePayFlag(list.get(i));
                        }
                    }
                }
                break;
        }

        try {
            ExcelUtil.export(exportDto.getFilename(), "sheet1", title, contents, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public ResultJson importMember(MultipartFile file, String json) {
        TParkGroupMemberinfoRequest request = JSON.parseObject(json, TParkGroupMemberinfoRequest.class);
        String filePath = file.getOriginalFilename();
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            return resultJson.setCode(900).setMsg("文件不是excel类型").setData(null);
        }
        InputStream fis = null;
        try {
            fis = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        Workbook wookbook = null;
        try {
            //2007版本的excel，用.xlsx结尾

            wookbook = new XSSFWorkbook(fis);//得到工作簿
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);

        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();

        TParkMemberGroup byId = itParkMemberGroupService.getById(request.getMemberGroupId());
        if (byId.getFeesroleinfoId() == null || ("").equals(byId.getFeesroleinfoId())) {
            return resultJson.setCode(900).setMsg("导入失败,卡组需先配置计费").setData(null);
        }
        TCarFeesroleinfo feeById = itCarFeesroleinfoService.getById(byId.getFeesroleinfoId());
        if (feeById.getCardType() == 5) {
            return resultJson.setCode(900).setMsg("卡组计费为其他卡,不予导入").setData(null);
        }
        String parkCode = byId.getParkCode();
        List<String> memberIds = itParkMemberGroupRelationService.getMemberIdByChild(request.getMemberGroupId());
        String join = String.join(",", memberIds);
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        String now = format.format(new Date());

        List<ImportMemberDto> list = new ArrayList<>();
        for (int i = 1; i <= totalRowNum; i++) {
            ImportMemberDto dto = new ImportMemberDto();
            Row row = sheet.getRow(i);
            try {
                row.getCell((short) 0).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 1).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 2).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 3).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 4).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 5).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 9).setCellType(Cell.CELL_TYPE_STRING);
            } catch (Exception e) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行数据不能为空").setData(null);
            }
            String yn = row.getCell((short) 9).getStringCellValue().toUpperCase();
            if (!yn.equals("Y") && !yn.equals("N")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行是否已缴费输入错误").setData(null);
            }

            String owner_name = row.getCell((short) 0).getStringCellValue();
            if (!owner_name.matches("[u4E00-\u9FA5]{2,20}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行姓名格式错误").setData(null);
            }
            String tel_no = row.getCell((short) 1).getStringCellValue();
            if (!tel_no.matches("[1][35789]\\d{9}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行联系电话格式错误").setData(null);
            }
            String seat_num = row.getCell((short) 2).getStringCellValue();
            if (!seat_num.matches("[0-9]+")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行车位数格式错误").setData(null);
            }
            String buy_num = row.getCell((short) 3).getStringCellValue();
            if (!buy_num.matches("[0-9]+")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行份数格式错误").setData(null);
            }
            String car_type = row.getCell((short) 4).getStringCellValue();
            int caseCarType = caseCarType(car_type);
            String car_id = row.getCell((short) 5).getStringCellValue();
            if (!car_id.equals(car_id.toUpperCase())) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行车牌格式错误").setData(null);
            }
            if (!car_id.matches("^[京津沪渝冀豫云辽黑湘皖鲁新苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼使领WJ]{1}[A-HJ-NP-Z]{1}[A-HJ-NP-Z0-9]{4}[A-HJ-NP-Z0-9挂学警港澳]{1,3}$")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行车牌格式错误").setData(null);
            }
            if (yn.equals("N")) {
                try {
                    row.getCell((short) 8).setCellType(Cell.CELL_TYPE_STRING);
                } catch (Exception e) {
                    return resultJson.setCode(900).setMsg("导入失败,第" + i + "行过期时间不能为空").setData(null);
                }

                String expire_time = row.getCell((short) 8).getStringCellValue();
                if (!isDate(expire_time)) {
                    return resultJson.setCode(900).setMsg("导入失败,第" + i + "行过期时间格式错误").setData(null);
                }

                if (memberIds.size() > 0) {
                    List<Map<String, Object>> checkCarId = itParkGroupCarinfoService.checkCarId(car_id, caseCarType, join);
                    if (checkCarId.size() > 0) {
                        return resultJson.setCode(ResultCode.FAIL).setMsg("导入失败,第" + i + "行车牌已存在").setData(null);
                    }
                }
                dto.setYn("N");
                dto.setExpire_time(expire_time.replaceAll("-", ""));
            }
            if (yn.equals("Y")) {
                try {
                    row.getCell((short) 6).setCellType(Cell.CELL_TYPE_STRING);
                } catch (Exception e) {
                    return resultJson.setCode(900).setMsg("导入失败,第" + i + "行开始时间不能为空").setData(null);
                }
                try {
                    row.getCell((short) 7).setCellType(Cell.CELL_TYPE_STRING);
                } catch (Exception e) {
                    return resultJson.setCode(900).setMsg("导入失败,第" + i + "行收费金额不能为空").setData(null);
                }

                String startTime = row.getCell((short) 6).getStringCellValue();
                String amount = row.getCell((short) 7).getStringCellValue();
                if (!isDate(startTime)) {
                    return resultJson.setCode(900).setMsg("导入失败,第" + i + "行开始时间格式错误").setData(null);
                }
                if (!amount.matches("^(\\d)?\\d+(\\.\\d+)?$")) {
                    return resultJson.setCode(900).setMsg("导入失败,第" + i + "行收费金额格式错误").setData(null);
                }
                if ((feeById.getMonthValue().multiply(new BigDecimal(buy_num))).compareTo(new BigDecimal(amount)) != 0) {
                    return resultJson.setCode(900).setMsg("导入失败,第" + i + "行收费金额与(套餐金额*份数)不一致").setData(null);
                }

                if (memberIds.size() > 0) {
                    List<Map<String, Object>> checkCarId = itParkGroupCarinfoService.checkCarId(car_id, caseCarType, join);
                    if (checkCarId.size() > 0) {
                        return resultJson.setCode(ResultCode.FAIL).setMsg("导入失败,第" + i + "行车牌已存在").setData(null);
                    }
                }
                dto.setYn("Y");
                dto.setAmount(amount);
                dto.setSatrtTime(startTime.replaceAll("-", ""));
                try {
                    dto.setEndTime(getDate(feeById.getCardType(), Integer.parseInt(buy_num), startTime.replaceAll("-", "")));
                } catch (ParseException e) {
                    return resultJson.setCode(ResultCode.FAIL).setMsg("导入失败,第" + i + "行时间格式化异常").setData(null);
                }


            }
            dto.setOwner_name(owner_name);
            dto.setTel_no(tel_no);
            dto.setCar_id(car_id);
            dto.setBuy_num(buy_num);
            dto.setCar_type(String.valueOf(caseCarType));
            dto.setSeat_num(seat_num);
            list.add(dto);

        }

        Map<String, Object> telMap = new HashMap<>();
        LinkedHashMap<String, Object> map1 = new LinkedHashMap<>();
        for (int i = 0; i < list.size(); i++) {
            String owner_name = list.get(i).getOwner_name();
            String tel_no = list.get(i).getTel_no();
            String car_id = list.get(i).getCar_id();
            String expire_time = list.get(i).getExpire_time();
            String buy_num = list.get(i).getBuy_num();
            String car_type = list.get(i).getCar_type();
            String seat_num = list.get(i).getSeat_num();
            String startTime = list.get(i).getSatrtTime() == null ? "0" : list.get(i).getSatrtTime();
            String endTime = list.get(i).getEndTime() == null ? "0" : list.get(i).getEndTime();
            String amount = list.get(i).getAmount() == null ? "0" : list.get(i).getAmount();
            String yn = list.get(i).getYn();

            //校验车位数和车牌数
            List<String> telList = list.stream().map(ImportMemberDto::getTel_no).collect(Collectors.toList());
            int telCount = Collections.frequency(telList, tel_no);
            if (telCount < Integer.valueOf(seat_num)) {
                return resultJson.setCode(900).setMsg("导入失败,第" + (i + 1) + "行车牌数必须大于车位数").setData(null);
            }
            List<Map<String, String>> maps = new ArrayList<>();
            if (telMap.get("telKey") == null || !telMap.get("telKey").equals(tel_no)) {
                Map<String, Object> map = new HashMap<>();
                map.put("park_code", parkCode);
                map.put("agent_id", request.getAgentId());
                map.put("operator", request.getOperId());
                map.put("owner_name", owner_name);
                map.put("room_no", "");
                map.put("amount", amount);
                map.put("remark", "");
                map.put("member_level", "1");
                map.put("tel_no", tel_no);
                map.put("seat_num", seat_num);
                map.put("region_code", "");
                map.put("start_date", startTime);
                map.put("end_date", endTime);
                if (yn.equals("N")) {
                    map.put("member_type", 2);
                    map.put("expire_time", expire_time);
                } else {
                    map.put("member_type", 1);
                }

                try {
                    log.info("importMember-新增会员:c接口请求参数,param======>" + map);
                    maps = kesbApiService.queryKesbApiList(Constant.MEMBER_INFO, map);
                    log.info("importMember-新增会员:c接口返回参数,param======>" + maps);
                    ResultJson result = checkReturn(maps, map1, "member_info" + i);
                    if (result != null) {
                        return result;
                    }
                } catch (KesbException e) {
                    rollBack(map1);
                    return resultJson.setCode(ResultCode.FAIL).setMsg(e.getMessage()).setData(null);
                } catch (Exception e) {
                    log.error(Constant.ERROR_MSG_INSERT_MEMBER, e);
                    rollBack(map1);
                    throw new CustomException(Constant.ERROR_MSG_INSERT_MEMBER);
                }
                String id = this.returnId(maps);
                if (StringUtils.isNotBlank(id)) {
                    telMap.put("telKey", tel_no);
                    telMap.put("id", id);

                    TParkMemberGroupRelation tParkMemberGroupRelation = new TParkMemberGroupRelation();
                    tParkMemberGroupRelation.setGroupId(request.getMemberGroupId());
                    tParkMemberGroupRelation.setCreateTime(Long.valueOf(now));
                    tParkMemberGroupRelation.setMemberId(Long.valueOf(id));
                    tParkMemberGroupRelation.setUpdateTime(Long.valueOf(now));
                    itParkMemberGroupRelationService.save(tParkMemberGroupRelation);

                    for (int j = 0; j < Integer.valueOf(seat_num); j++) {
                        Map<String, Object> seatmap = new HashMap<>();
                        seatmap.put("id", "");
                        seatmap.put("role_name", feeById == null ? "" : feeById.getRoleName());
                        seatmap.put("region_name", "");
                        seatmap.put("fees_roleid", byId.getFeesroleinfoId());
                        seatmap.put("buy_num", buy_num);
                        seatmap.put("start_date", startTime);
                        seatmap.put("end_date", endTime);
                        seatmap.put("owner_money", feeById == null ? "" : feeById.getMonthValue());
                        seatmap.put("car_type", car_type);
                        seatmap.put("operate", 1);
                        seatmap.put("group_id", telMap.get("id"));
                        seatmap.put("operator", request.getOperId());
                        seatmap.put("amount", amount);
                        if (yn.equals("N")) {
                            seatmap.put("member_type", 2);
                        } else {
                            seatmap.put("member_type", 1);
                        }
                        try {
                            log.info("importMember-新增车位:c接口请求参数,param======>" + seatmap);
                            maps = kesbApiService.queryKesbApiList(Constant.MEMBER_SEAT, seatmap);
                            log.info("importMember-新增车位:c接口返回参数,param======>" + maps);
                            ResultJson result = checkReturn(maps, map1, i + "seat_info" + j);
                            if (result != null) {
                                return result;
                            }
                        } catch (KesbException e) {
                            rollBack(map1);
                            return resultJson.setCode(ResultCode.FAIL).setMsg(e.getMessage()).setData(null);
                        } catch (Exception e) {
                            log.error(Constant.ERROR_MSG_INSERT_SEAT, e);
                            rollBack(map1);
                            throw new CustomException(Constant.ERROR_MSG_INSERT_SEAT);
                        }
                    }
                    /*if (yn.equals("N")) {
                        itParkGroupMemberinfoService.updateById(new TParkGroupMemberinfo().setId(Long.valueOf(id)).setMember_type(2).setExpireTime(Long.valueOf(expire_time)).setSeatNum(Integer.valueOf(seat_num)));
                    }*/

                }
            }
            list.get(i).setMemberinfoId(telMap.get("id").toString());
            Map<String, Object> carmap = new HashMap<>();
            carmap.put("id", "");
            carmap.put("car_num", car_id);
            carmap.put("car_type", car_type);
            carmap.put("operate", 1);
            carmap.put("group_id", telMap.get("id"));
            carmap.put("car_id", car_id);
            carmap.put("operator", request.getOperId());
            try {
                log.info("importMember-新增车牌:c接口请求参数,param======>" + carmap);
                maps = kesbApiService.queryKesbApiList(Constant.MEMBER_CARID, carmap);
                log.info("importMember-新增车牌:c接口返回参数,param======>" + maps);
                ResultJson result = checkReturn(maps, map1, "carid_info" + i);
                if (result != null) {
                    return result;
                }
            } catch (KesbException e) {
                rollBack(map1);
                return resultJson.setCode(ResultCode.FAIL).setMsg(e.getMessage()).setData(null);
            } catch (Exception e) {
                log.error(Constant.ERROR_MSG_INSERT_CARID, e);
                rollBack(map1);
                throw new CustomException(Constant.ERROR_MSG_INSERT_CARID);
            }

        }

        for (int i = 0; i < list.size(); i++) {
            String yn = list.get(i).getYn();
            String member_info_id = list.get(i).getMemberinfoId();
            if (yn.equals("Y")) {
                tParkGroupMemberinfoServiceImpl.newMemberMq(member_info_id, Constant.OPER_CAR, 1, false);
            }
        }

        return resultJson.setCode(ResultCode.SUCCESS).setMsg(ResultCode.SUCCESS_MSG).setData(null);
    }

    @Override
    public ResultJson importDeferMember(MultipartFile file, String json) {
        TParkGroupMemberinfoRequest request = JSON.parseObject(json, TParkGroupMemberinfoRequest.class);
        Long operId = request.getOperId();
        Long memberGroupId = request.getMemberGroupId();
        //停车场cust_id
        Long custId = request.getCustId();
        String feesRoleid = request.getFeesRoleid();
        if (("").equals(feesRoleid) || feesRoleid == null) {
            return resultJson.setCode(900).setMsg("卡组需先配置计费").setData(null);
        }
        TCarFeesroleinfo feesroleinfo = itCarFeesroleinfoService.getById(feesRoleid);
        if (feesroleinfo.getCardType() == 5) {
            return resultJson.setCode(900).setMsg("卡组计费为其他卡,不予导入").setData(null);
        }
        String filePath = file.getOriginalFilename();
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            return resultJson.setCode(900).setMsg("文件不是excel类型").setData(null);
        }
        InputStream fis = null;
        try {
            fis = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        Workbook wookbook = null;
        try {
            //2007版本的excel，用.xlsx结尾

            wookbook = new XSSFWorkbook(fis);//得到工作簿
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);

        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();

        List<Map<String, String>> deferMemberSeatList = new ArrayList<>();
        for (int i = 1; i <= totalRowNum; i++) {
            Row row = sheet.getRow(i);
            try {
                row.getCell((short) 0).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 1).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 2).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell((short) 3).setCellType(Cell.CELL_TYPE_STRING);
            } catch (Exception e) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行数据不能为空").setData(null);
            }

            String owner_name = row.getCell((short) 0).getStringCellValue();
            if (!owner_name.matches("[u4E00-\u9FA5]{2,20}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行姓名格式错误").setData(null);
            }
            String tel_no = row.getCell((short) 1).getStringCellValue();
            if (!tel_no.matches("[1][35789]\\d{9}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行联系电话格式错误").setData(null);
            }
            String new_start_date = row.getCell((short) 2).getStringCellValue();
            if (isDate(new_start_date)) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行日期格式错误").setData(null);
            }
            String buy_num = row.getCell((short) 3).getStringCellValue();
            if (!buy_num.matches("[0-9]+")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行份数格式错误").setData(null);
            }
            List<Map<String, String>> list = itParkMemberGroupRelationService.checkTel(memberGroupId, tel_no);
            if (list.size() > 1) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行同一卡组不能存在相同电话号码").setData(null);
            }
            if (list.size() == 0) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行卡组下无此会员").setData(null);
            }
            if (!list.get(0).get("owner_name").equals(owner_name)) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行电话号码与车主姓名不一致").setData(null);
            }
            if (Integer.valueOf(String.valueOf(list.get(0).get("total"))) > 1) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行目前只支持延期一卡一车").setData(null);
            }
            if (Integer.valueOf(String.valueOf(list.get(0).get("total"))) == 0) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行无可延期车位").setData(null);
            }
            Map<String, String> map = new HashMap<>();
            map.put("seat_id", list.get(0).get("seat_id"));
            map.put("member_id", list.get(0).get("id"));
            map.put("buy_num", buy_num);
            map.put("cust_id", String.valueOf(custId));
            map.put("fees_roleid", feesRoleid);
            map.put("member_group_id", String.valueOf(memberGroupId));
            map.put("start_date", new_start_date.replaceAll("-", ""));
            deferMemberSeatList.add(map);
        }

        for (int i = 0; i < deferMemberSeatList.size(); i++) {
            Map<String, String> deferMemberSeat = deferMemberSeatList.get(i);

            String end_date = "";
            try {
                end_date = getDate(feesroleinfo.getCardType(), Integer.valueOf(deferMemberSeat.get("buy_num")), deferMemberSeat.get("start_date"));
            } catch (ParseException e) {
                return resultJson.setCode(900).setMsg("第" + (i + 1) + "行导入失败,日期格式化异常").setData(null);
            }

            Map<String, String> deferConfirmMemberSeatMap = new HashMap<>();
            deferConfirmMemberSeatMap.put("operator", String.valueOf(operId));
            deferConfirmMemberSeatMap.put("id", deferMemberSeat.get("seat_id"));
            deferConfirmMemberSeatMap.put("new_end_date", end_date);
            deferConfirmMemberSeatMap.put("buy_num", deferMemberSeat.get("buy_num"));
            deferConfirmMemberSeatMap.put("amount", String.valueOf(feesroleinfo.getMonthValue().multiply(new BigDecimal(deferMemberSeat.get("buy_num")))));
            deferConfirmMemberSeatMap.put("group_id", deferMemberSeat.get("member_id"));
            deferConfirmMemberSeatMap.put("fees_roleid", feesRoleid);
            deferConfirmMemberSeatMap.put("role_name", feesroleinfo.getRoleName());
            deferConfirmMemberSeatMap.put("new_start_date", deferMemberSeat.get("start_date"));
            ResultJson result = itParkGroupMemberinfoService.deferConfirmMemberSeat(JSON.toJSONString(deferConfirmMemberSeatMap));
            if (result.getCode() != 200) {
                return resultJson.setCode(900).setMsg("导入失败,第" + (i + 1) + "行延期失败:" + result.getMsg()).setData(null);
            }
        }

        return resultJson.setCode(200).setMsg(ResultCode.SUCCESS_MSG).setData(null);
    }

    @Override
    public ResultJson importWhiteMember(MultipartFile file, String json) {
        TParkGroupMemberinfoRequest request = JSON.parseObject(json, TParkGroupMemberinfoRequest.class);
        String filePath = file.getOriginalFilename();
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            return resultJson.setCode(900).setMsg("文件不是excel类型").setData(null);
        }
        InputStream fis = null;
        try {
            fis = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        Workbook wookbook = null;
        try {
            //2007版本的excel，用.xlsx结尾

            wookbook = new XSSFWorkbook(fis);//得到工作簿
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);

        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();

        TParkMemberGroup byId = itParkMemberGroupService.getById(request.getMemberGroupId());
        if (byId.getFeesroleinfoId() == null || ("").equals(byId.getFeesroleinfoId())) {
            return resultJson.setCode(900).setMsg("导入失败,卡组需先配置计费").setData(null);
        }
        TCarFeesroleinfo feeById = itCarFeesroleinfoService.getById(byId.getFeesroleinfoId());
        if (feeById.getCardType() == 5) {
            return resultJson.setCode(900).setMsg("卡组计费为其他卡,不予导入").setData(null);
        }
        String parkCode = byId.getParkCode();
        List<String> memberIds = itParkMemberGroupRelationService.getMemberIdByChild(request.getMemberGroupId());
        String join = String.join(",", memberIds);
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        String now = format.format(new Date());

        List<ImportMemberDto> list = new ArrayList<>();
        for (int i = 1; i <= totalRowNum; i++) {
            ImportMemberDto dto = new ImportMemberDto();
            Row row = sheet.getRow(i);
            try {
                row.getCell((short) 0).setCellType(Cell.CELL_TYPE_STRING); //车主姓名
                row.getCell((short) 1).setCellType(Cell.CELL_TYPE_STRING); //联系电话
                row.getCell((short) 2).setCellType(Cell.CELL_TYPE_STRING); //车辆类型
                row.getCell((short) 3).setCellType(Cell.CELL_TYPE_STRING); //绑定车牌
                row.getCell((short) 4).setCellType(Cell.CELL_TYPE_STRING); //开始日期
                row.getCell((short) 5).setCellType(Cell.CELL_TYPE_STRING); //结束日期

            } catch (Exception e) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行数据不能为空").setData(null);
            }
//            String yn = row.getCell((short) 9).getStringCellValue().toUpperCase();
//            if (!yn.equals("Y") && !yn.equals("N")) {
//                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行是否已缴费输入错误").setData(null);
//            }

            String owner_name = row.getCell((short) 0).getStringCellValue();
            if (!owner_name.matches("[u4E00-\u9FA5]{2,20}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行姓名格式错误").setData(null);
            }
            String tel_no = row.getCell((short) 1).getStringCellValue();
            if (!tel_no.matches("[1][35789]\\d{9}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行联系电话格式错误").setData(null);
            }
            String car_type = row.getCell((short) 2).getStringCellValue();
            int caseCarType = caseCarType(car_type);
            String car_id = row.getCell((short) 3).getStringCellValue();
            if (!car_id.equals(car_id.toUpperCase())) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行车牌格式错误").setData(null);
            }
            if (!car_id.matches("^[京津沪渝冀豫云辽黑湘皖鲁新苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼使领WJ]{1}[A-HJ-NP-Z]{1}[A-HJ-NP-Z0-9]{4}[A-HJ-NP-Z0-9挂学警港澳]{1,3}$")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行车牌格式错误").setData(null);
            }

            String start_time = row.getCell((short) 4).getStringCellValue();
            if (!isDate(start_time)) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行开始日期格式错误").setData(null);
            }

            if (memberIds.size() > 0) {
                List<Map<String, Object>> checkCarId = itParkGroupCarinfoService.checkCarId(car_id, caseCarType, join);
                if (checkCarId.size() > 0) {
                    return resultJson.setCode(ResultCode.FAIL).setMsg("导入失败,第" + i + "行车牌已存在").setData(null);
                }
            }
            dto.setSatrtTime(start_time.replaceAll("-", ""));

            String end_time = row.getCell((short) 5).getStringCellValue();
            if (!isDate(end_time)) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行结束日期格式错误").setData(null);
            }
            dto.setEndTime(end_time.replaceAll("-", ""));

            String remark = "";
            if (row.getCell((short) 6) != null) {
                row.getCell((short) 6).setCellType(Cell.CELL_TYPE_STRING); //备注
                remark = row.getCell((short) 6).getStringCellValue();
            }

            String memberType;
            if (request.getFun() == 0) {  //白名单
                if (row.getCell((short) 7) != null) {
                    row.getCell((short) 7).setCellType(Cell.CELL_TYPE_STRING); //是否需要审核
                    memberType = "Y".equals(row.getCell((short) 7).getStringCellValue()) ? "2" : "1";
                } else {
                    memberType = "1";
                }
            } else {   //黑名单
                memberType = "1";
            }

            dto.setOwner_name(owner_name);
            dto.setTel_no(tel_no);
            dto.setCar_id(car_id);
            dto.setCar_type(String.valueOf(caseCarType));
            dto.setRemark(remark);
            dto.setMemberType(memberType);
            list.add(dto);
        }

        Map<String, Object> telMap = new HashMap<>();
        for (int i = 0; i < list.size(); i++) {
            String owner_name = list.get(i).getOwner_name();
            String tel_no = list.get(i).getTel_no();
            String car_id = list.get(i).getCar_id();
//            String expire_time = list.get(i).getExpire_time();
//            String buy_num = list.get(i).getBuy_num();
            String car_type = list.get(i).getCar_type();
//            String seat_num = list.get(i).getSeat_num();
            String startTime = list.get(i).getSatrtTime() == null ? "0" : list.get(i).getSatrtTime();
            String endTime = list.get(i).getEndTime() == null ? "0" : list.get(i).getEndTime();
//            String amount = list.get(i).getAmount() == null ? "0" : list.get(i).getAmount();
//            String yn = list.get(i).getYn();
            String remark = list.get(i).getRemark();
            String member_Type = list.get(i).getMemberType();

            //校验车位数和车牌数
//            List<String> telList = list.stream().map(ImportMemberDto::getTel_no).collect(Collectors.toList());
////            int telCount = Collections.frequency(telList, tel_no);
////            if (telCount < Integer.valueOf(seat_num)) {
////                return resultJson.setCode(900).setMsg("导入失败,第" + (i + 1) + "行车牌数必须大于车位数").setData(null);
////            }
            List<Map<String, String>> maps = new ArrayList<>();
            if (telMap.get("telKey") == null || !telMap.get("telKey").equals(tel_no)) {
                Long groupMemberinfoId = this.getTableNo(1);
                Long groupCarinfoId = this.getTableNo(2);
                Long operateLogId = this.getTableNo(4);

                TParkGroupMemberinfo tParkGroupMemberinfo = new TParkGroupMemberinfo();
                tParkGroupMemberinfo.setId(groupMemberinfoId);
                tParkGroupMemberinfo.setNodeId(1);
                tParkGroupMemberinfo.setAgentId(request.getAgentId());
                tParkGroupMemberinfo.setParkCode(parkCode);
                tParkGroupMemberinfo.setOwnerName(owner_name);
                tParkGroupMemberinfo.setTelNo(tel_no);
                tParkGroupMemberinfo.setMemberLevel(1);
                tParkGroupMemberinfo.setStartDate(Integer.valueOf(startTime));
                tParkGroupMemberinfo.setEndDate(Integer.valueOf(endTime));
                tParkGroupMemberinfo.setDelflag(1);
                tParkGroupMemberinfo.setCreateTime(Long.valueOf(now));
                tParkGroupMemberinfo.setUpdateTime(Long.valueOf(now));
                tParkGroupMemberinfo.setRemark(remark);

                //  if (request.getFun() == 0) {
                tParkGroupMemberinfo.setMember_type(Integer.valueOf(member_Type));
                //}
                boolean save = itParkGroupMemberinfoService.save(tParkGroupMemberinfo);
                if (!save) {
                    return resultJson.setCode(ResultCode.FAIL).setMsg(ResultCode.FAIL_MSG).setData(null);
                }
                TParkGroupCarinfo tParkGroupCarinfo = new TParkGroupCarinfo();
                tParkGroupCarinfo.setId(groupCarinfoId);
                tParkGroupCarinfo.setNodeId(0);
                tParkGroupCarinfo.setCarId(car_id);
                tParkGroupCarinfo.setGroupId(groupMemberinfoId);
                tParkGroupCarinfo.setCarType(Integer.valueOf(car_type));
                tParkGroupCarinfo.setCreateTime(Long.valueOf(now));
                tParkGroupCarinfo.setDelflag(1);
                boolean save1 = itParkGroupCarinfoService.save(tParkGroupCarinfo);
                if (!save1) {
                    return resultJson.setCode(ResultCode.FAIL).setMsg(ResultCode.FAIL_MSG).setData(null);
                }
                TParkMemberGroupRelation tParkMemberGroupRelation = new TParkMemberGroupRelation();
                tParkMemberGroupRelation.setGroupId(byId.getId());
                tParkMemberGroupRelation.setCreateTime(Long.valueOf(now));
                tParkMemberGroupRelation.setMemberId(groupMemberinfoId);
                tParkMemberGroupRelation.setUpdateTime(Long.valueOf(now));
                boolean save2 = itParkMemberGroupRelationService.save(tParkMemberGroupRelation);
                if (!save2) {
                    return resultJson.setCode(ResultCode.FAIL).setMsg(ResultCode.FAIL_MSG).setData(null);
                }
                String operateRemark = "";
                // String memberType = "";
                //白名单请求时
                if (request.getFun() == 0) {
                    if (("2").equals(member_Type)) {
                        operateRemark = "新增待审核白名单,手机号:" + tel_no + ",车主姓名:" + owner_name + ",开始时间:" + startTime + ",结束时间:" + endTime;
                    } else if (("1".equals(member_Type))) {
                        operateRemark = "新增已审核白名单,手机号:" + tel_no + ",车主姓名:" + owner_name + ",开始时间:" + startTime + ",结束时间:" + startTime;
                    }
                }
                //黑名单请求时
                if (request.getFun() == 1) {
                    member_Type = Constant.MEMBER_TYPE_BLACK;
                    operateRemark = "新增黑名单,手机号:" + tel_no + ",车主姓名:" + owner_name + ",开始时间:" + startTime + ",结束时间:" + startTime;
                }

                TParkGroupOperateLog tParkGroupOperateLog = new TParkGroupOperateLog()
                        .setId(operateLogId)
                        .setNodeId(0)
                        .setGroupId(groupMemberinfoId)
                        .setSeatId(0L)
                        .setAgentId(0L)
                        .setParkCode(parkCode)
                        .setRegionCode("")
                        .setCarId(car_id)
                        .setCarType(Integer.valueOf(car_type))
                        .setRemark(operateRemark)
                        .setOperateTime(Long.valueOf(now))
                        .setOperatorNo(request.getOperId() == null ? "" : request.getOperId().toString())
                        .setOperateType(1)
                        .setOperateDay(0);

                boolean save4 = itParkGroupOperateLogService.save(tParkGroupOperateLog);
                if (!save4) {
                    return resultJson.setCode(ResultCode.FAIL).setMsg(ResultCode.FAIL_MSG).setData(null);
                }

                if ((request.getFun() == 0 && member_Type.equals("1"))) {
                    Long memberamountinfoId = this.getTableNo(3);
                    TCarMemberamountinfo tCarMemberamountinfo = new TCarMemberamountinfo()
                            .setId(memberamountinfoId)
                            .setNodeId(1)
                            .setCustId(request.getCustId() == null ? 0 : request.getCustId())
                            .setParkCode(parkCode)
//                    .setCarId(request.getCarId())
                            .setAmountType(3)
                            .setFeesRoleid(0L)
                            .setRoleName("")
                            .setMonthValue(new BigDecimal(0))
                            .setUpTime(Long.valueOf(now))
                            .setAmountNum(new BigDecimal(0))
                            .setStartDate(Integer.valueOf(startTime))
                            .setEndDate(Integer.valueOf(endTime))
                            .setOperId(request.getOperId() == null ? 0 : request.getOperId())
                            .setRemark(remark)
                            .setRegionCode("")
                            .setBuyOri(0)
                            .setPayId("0")
                            .setMemberId(groupMemberinfoId)
                            .setSeatId(null);

                    boolean save3 = itCarMemberamountinfoService.save(tCarMemberamountinfo);
                    if (!save3) {
                        return resultJson.setCode(ResultCode.FAIL).setMsg(ResultCode.FAIL_MSG).setData(null);
                    }
                }

                if (request.getFun() == 1 || (request.getFun() == 0 && member_Type.equals("1"))) {
                    //下发黑名单或者已审核的白名单
                    MemberinfoMqDto dto = new MemberinfoMqDto();
                    dto.setCar_id(car_id);
                    dto.setCar_type(car_type.toString().replaceAll("8", "5"));
                    dto.setEnd_date(endTime);
                    dto.setGroup_id(groupMemberinfoId.toString());
                    dto.setId(groupMemberinfoId.toString());
                    dto.setMember_type(member_Type);
                    dto.setOper_type(Constant.OPER_CAR);
                    dto.setOwner_name(owner_name);
                    dto.setPark_code(parkCode);
                    dto.setRemark(remark);
                    dto.setSeat_num("0");
                    dto.setSend_time(now);
                    dto.setStart_date(startTime);

                    mqService.mqSend(JSON.toJSONString(dto), "member." + parkCode);

                    QueryWrapper<TParkMemberGroupRelation> queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("member_id", groupMemberinfoId);
                    TParkMemberGroupRelation one = itParkMemberGroupRelationService.getOne(queryWrapper);
                    TParkMemberGroupRelationDto tParkMemberGroupRelationDto = DtoUtil.convertObject(one, TParkMemberGroupRelationDto.class);
                    tParkMemberGroupRelationDto.setType(1);
                    //下发关系表
                    mqService.mqSend(JSON.toJSONString(tParkMemberGroupRelationDto), "memberGroupRelation." + parkCode);
                }

//                String id = this.returnId(maps);
//                if (StringUtils.isNotBlank(id)) {
//                    telMap.put("telKey", tel_no);
//                    telMap.put("id", id);
//
//                    TParkMemberGroupRelation tParkMemberGroupRelation = new TParkMemberGroupRelation();
//                    tParkMemberGroupRelation.setGroupId(request.getMemberGroupId());
//                    tParkMemberGroupRelation.setCreateTime(Long.valueOf(now));
//                    tParkMemberGroupRelation.setMemberId(Long.valueOf(id));
//                    tParkMemberGroupRelation.setUpdateTime(Long.valueOf(now));
//                    itParkMemberGroupRelationService.save(tParkMemberGroupRelation);
//
//                }
            }
        }
        return resultJson.setCode(ResultCode.SUCCESS).setMsg(ResultCode.SUCCESS_MSG).setData(null);
    }

    @Override
    public ResultJson importDeferWhiteMember(MultipartFile file, String json) {
        TParkGroupMemberinfoRequest request = JSON.parseObject(json, TParkGroupMemberinfoRequest.class);
        Long operId = request.getOperId();
        Long memberGroupId = request.getMemberGroupId();
        //停车场cust_id
        Long custId = request.getCustId();
        String feesRoleid = request.getFeesRoleid();
        if (("").equals(feesRoleid) || feesRoleid == null) {
            return resultJson.setCode(900).setMsg("卡组需先配置计费").setData(null);
        }
        TCarFeesroleinfo feesroleinfo = itCarFeesroleinfoService.getById(feesRoleid);
        if (feesroleinfo.getCardType() == 5) {
            return resultJson.setCode(900).setMsg("卡组计费为其他卡,不予导入").setData(null);
        }
        String filePath = file.getOriginalFilename();
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            return resultJson.setCode(900).setMsg("文件不是excel类型").setData(null);
        }
        InputStream fis = null;
        try {
            fis = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        Workbook wookbook = null;
        try {
            //2007版本的excel，用.xlsx结尾

            wookbook = new XSSFWorkbook(fis);//得到工作簿
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);

        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();

        List<Map<String, String>> deferMemberSeatList = new ArrayList<>();
        for (int i = 1; i <= totalRowNum; i++) {
            Row row = sheet.getRow(i);
            try {
                row.getCell((short) 0).setCellType(Cell.CELL_TYPE_STRING); //姓名
                row.getCell((short) 1).setCellType(Cell.CELL_TYPE_STRING); //手机号
                row.getCell((short) 2).setCellType(Cell.CELL_TYPE_STRING); //结束时间
//                row.getCell((short) 3).setCellType(Cell.CELL_TYPE_STRING);
            } catch (Exception e) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行数据不能为空").setData(null);
            }

            String owner_name = row.getCell((short) 0).getStringCellValue();
            if (!owner_name.matches("[u4E00-\u9FA5]{2,20}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行姓名格式错误").setData(null);
            }
            String tel_no = row.getCell((short) 1).getStringCellValue();
            if (!tel_no.matches("[1][35789]\\d{9}")) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行联系电话格式错误").setData(null);
            }
            String new_end_date = row.getCell((short) 2).getStringCellValue();
            if (!isDate(new_end_date)) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行日期格式错误").setData(null);
            }
//            String buy_num = row.getCell((short) 3).getStringCellValue();
//            if (!buy_num.matches("[0-9]+")) {
//                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行份数格式错误").setData(null);
//            }
            List<Map<String, String>> list = itParkMemberGroupRelationService.checkTel(memberGroupId, tel_no);
            if (list.size() > 1) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行同一卡组不能存在相同电话号码").setData(null);
            }
            if (list.size() == 0) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行卡组下无此会员").setData(null);
            }
            if (!list.get(0).get("owner_name").equals(owner_name)) {
                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行电话号码与车主姓名不一致").setData(null);
            }
//            if (Integer.valueOf(String.valueOf(list.get(0).get("total"))) > 1) {
//                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行目前只支持延期一卡一车").setData(null);
//            }
//            if (Integer.valueOf(String.valueOf(list.get(0).get("total"))) == 0) {
//                return resultJson.setCode(900).setMsg("导入失败,第" + i + "行无可延期车位").setData(null);
//            }
            Map<String, String> map = new HashMap<>();
            map.put("seat_id", list.get(0).get("seat_id"));
            map.put("member_id", String.valueOf(list.get(0).get("id")));
//            map.put("buy_num", buy_num);
            map.put("cust_id", String.valueOf(custId));
            map.put("fees_roleid", feesRoleid);
            map.put("member_group_id", String.valueOf(memberGroupId));
            map.put("end_date", new_end_date.replaceAll("-", ""));
            deferMemberSeatList.add(map);
        }

        for (int i = 0; i < deferMemberSeatList.size(); i++) {
            //暂停状态不予延期
//            TParkGroupMemberinfoRequest request = JSON.parseObject(json, TParkGroupMemberinfoRequest.class);
//            Long groupMemberinfoId = request.getGroupMemberinfoId();
            Long groupMemberinfoId = Long.parseLong(deferMemberSeatList.get(i).get("member_id"));
            String end_date = deferMemberSeatList.get(i).get("end_date");

            TParkGroupMemberinfo byId = itParkGroupMemberinfoService.getById(groupMemberinfoId);
            if (byId == null) {
                return resultJson.setCode(ResultCode.FAIL).setMsg(Constant.MSG_MEMBER_ERROR).setData(null);
            }

            QueryWrapper<TParkGroupCarinfo> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("group_id", groupMemberinfoId);
            queryWrapper.eq("delflag", 1);
            TParkGroupCarinfo one = null;
            try {
                one = itParkGroupCarinfoService.getOne(queryWrapper);

            } catch (Exception e) {
                log.error(Constant.ERROR_MSG_CARINFO, e);
                throw new CustomException(Constant.ERROR_MSG_CARINFO);
            }

            String date = end_date;
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
            String now = format.format(new Date());
            TParkGroupMemberinfo entity = new TParkGroupMemberinfo();
            entity.setId(Long.valueOf(groupMemberinfoId));
            entity.setEndDate(Integer.parseInt(date));
            entity.setUpdateTime(Long.valueOf(now));
            boolean b = itParkGroupMemberinfoService.updateById(entity);
            if (!b) {
                return resultJson.setCode(ResultCode.FAIL).setMsg(ResultCode.FAIL_MSG).setData(null);
            }
            Long operateLogId = this.getTableNo(4);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            Date parse = null;
            Date parse1 = null;
            try {
                parse = sdf.parse(byId.getEndDate().toString());
                parse1 = sdf.parse(end_date);
            } catch (ParseException e) {
                log.error(Constant.ERROR_MSG_DATE, e);
                e.printStackTrace();
            }
            String operateRemark = "延期白名单,手机号:" + byId.getTelNo() + ",车主姓名:" + byId.getOwnerName() + ",延期日期:" + end_date;
            TParkGroupOperateLog tParkGroupOperateLog = new TParkGroupOperateLog()
                    .setId(operateLogId)
                    .setNodeId(0)
                    .setGroupId(groupMemberinfoId)
                    .setSeatId(0L)
                    .setAgentId(0L)
                    .setParkCode(byId.getParkCode())
                    .setRegionCode("")
                    .setCarId(one == null ? "" : one.getCarId())
                    .setCarType(one == null ? 0 : one.getCarType())
                    .setRemark(operateRemark)
                    .setOperateTime(Long.valueOf(now))
                    .setOperatorNo(request.getOperId() == null ? "" : request.getOperId().toString())
                    .setOperateType(9)
                    .setOperateDay(cn.tnar.parkservice.util.DateUtils.differentDaysByMillisecond(parse, parse1));

            try {
                itParkGroupOperateLogService.save(tParkGroupOperateLog);
            } catch (Exception e) {
                log.error(Constant.ERROR_MSG_GROUPLOG, e);
                throw new CustomException(Constant.ERROR_MSG_GROUPLOG);
            }


            //下发白名单信息
            MemberinfoMqDto dto = new MemberinfoMqDto();
            dto.setCar_id(one.getCarId());
            dto.setCar_type(one.getCarType().toString().replaceAll("8", "5"));
            dto.setEnd_date(date);
            dto.setGroup_id(groupMemberinfoId.toString());
            dto.setId(groupMemberinfoId.toString());
            dto.setMember_type(Constant.MEMBER_TYPE_WHITE);
            dto.setOper_type(Constant.OPER_DEFER);
            dto.setOwner_name(byId.getOwnerName());
            dto.setPark_code(byId.getParkCode());
            dto.setRemark(byId.getRemark());
            dto.setSeat_num("0");
            dto.setSend_time(now);
            dto.setStart_date(byId.getStartDate().toString());
            mqService.mqSend(JSON.toJSONString(dto), "member." + byId.getParkCode());

//            Map<String, String> deferMemberSeat = deferMemberSeatList.get(i);
//
//            String end_date = "";
//            try {
//                end_date = getDate(feesroleinfo.getCardType(), Integer.valueOf(deferMemberSeat.get("buy_num")), deferMemberSeat.get("start_date"));
//            } catch (ParseException e) {
//                return resultJson.setCode(900).setMsg("第" + (i + 1) + "行导入失败,日期格式化异常").setData(null);
//            }
//
//            Map<String, String> deferConfirmMemberSeatMap = new HashMap<>();
//            deferConfirmMemberSeatMap.put("operator", String.valueOf(operId));
//            deferConfirmMemberSeatMap.put("id", deferMemberSeat.get("seat_id"));
//            deferConfirmMemberSeatMap.put("new_end_date", end_date);
//            deferConfirmMemberSeatMap.put("buy_num", deferMemberSeat.get("buy_num"));
//            deferConfirmMemberSeatMap.put("amount", String.valueOf(feesroleinfo.getMonthValue().multiply(new BigDecimal(deferMemberSeat.get("buy_num")))));
//            deferConfirmMemberSeatMap.put("group_id", deferMemberSeat.get("member_id"));
//            deferConfirmMemberSeatMap.put("fees_roleid", feesRoleid);
//            deferConfirmMemberSeatMap.put("role_name", feesroleinfo.getRoleName());
//            deferConfirmMemberSeatMap.put("new_start_date", deferMemberSeat.get("start_date"));
//            ResultJson result = itParkGroupMemberinfoService.deferConfirmMemberSeat(JSON.toJSONString(deferConfirmMemberSeatMap));
//            if (result.getCode() != 200) {
//                return resultJson.setCode(900).setMsg("导入失败,第" + (i + 1) + "行延期失败:" + result.getMsg()).setData(null);
//            }
        }
        return resultJson.setCode(ResultCode.SUCCESS).setMsg(ResultCode.SUCCESS_MSG).setData(null);
    }

    private static String getDate(Integer card_type, Integer buy_num, String start_date) throws ParseException {

        String end_date = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        switch (card_type) {
            case 1://月卡
                end_date = sdf.format(DateUtils.addMonths(sdf.parse(start_date), buy_num));
                break;
            case 2://季卡
                end_date = sdf.format(DateUtils.addMonths(sdf.parse(start_date), buy_num * 3));
                break;
            case 3://半年卡
                end_date = sdf.format(DateUtils.addMonths(sdf.parse(start_date), buy_num * 6));
                break;
            case 4://年卡
                end_date = sdf.format(DateUtils.addMonths(sdf.parse(start_date), buy_num * 12));
                break;
            case 6://日卡
                end_date = sdf.format(DateUtils.addDays(sdf.parse(start_date), buy_num));
                break;
        }

        return end_date;

    }

    //判断时间合法性
    private boolean isDate(String date) {
        try {
            if (date.length() != 10) {
                return false;
            }
            String substring = date.substring(4, 5);
            String substring2 = date.substring(7, 8);
            if (("--").equals(substring + substring2)) {
                return true;
            }
        } catch (Exception e) {
            return false;
        }

        String str = date.replaceAll("-", "");
        boolean result = true;
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
        try {
            format.setLenient(false);
            format.parse(String.valueOf(str));
        } catch (Exception e) {
            result = false;
        }
        return result;

    }


    private String returnId(List<Map<String, String>> maps) {
        String id = "";
        if (maps.get(0).containsKey("id")) {
            id = maps.get(0).get("id");
        } else if (maps.get(0).containsKey("group_id")) {
            id = maps.get(0).get("group_id");
        }
        return id;
    }

    private Long getTableNo(Integer num) {
        Long id = null;
        try {
            if (num == 1) {
                //  groupMemberinfoId
                id = itSysSnogeneralService.GetNextSno(500);
            }
            if (num == 2) {
                // groupCarinfoId
                id = itSysSnogeneralService.GetNextSno(501);
            }
            if (num == 3) {
                // memberamountinfoId
                id = itSysSnogeneralService.GetNextSno(53);
            }
            if (num == 4) {
                // operateLogId
                id = itSysSnogeneralService.GetNextSno(504);
            }

        } catch (Exception e) {
            log.error(Constant.ERROR_MSG_SNO, e);
            throw new CustomException(Constant.ERROR_MSG_SNO);
        }
        return id;
    }

    private int caseCarType(String carType) {
        int type;
        switch (carType) {
            case "小型车(蓝)":
                type = 1;
                break;
            case "中型车":
                type = 2;
                break;
            case "大型车(黄)":
                type = 3;
                break;
            case "电动车":
                type = 4;
                break;
            case "绿牌车":
                type = 6;
                break;
            case "白牌车":
                type = 7;
                break;
            case "黑牌车":
                type = 8;
                break;

            default:
                type = 0;
                break;
        }
        return type;

    }


    private String getCarTypeStr(String carType) {
        String carTypeStr = "";
        switch (carType) {
            case "1":
                carTypeStr = "蓝牌车";
                break;
            case "2":
                carTypeStr = "中型车";
                break;
            case "3":
                carTypeStr = "黄牌车";
                break;
            case "4":
                carTypeStr = "电动车";
                break;
            case "6":
                carTypeStr = "绿牌车";
                break;
            case "7":
                carTypeStr = "白牌车";
                break;
            case "8":
                carTypeStr = "黑牌车";
                break;
            default:
                carTypeStr = "";
                break;
        }
        return carTypeStr;
    }

    private String casePayType(int payType) {
        switch (payType) {
            case 1:
                return "微信";
            case 2:
                return "支付宝";
            case 3:
                return "市民卡";
            case 4:
                return "储值卡余额（自助缴费）";
            case 5:
                return "商户面余额支付";
            case 6:
                return "无感支付（建行）";
            case 8:
                return "无感支付（农行）";
            case 9:
                return "无感支付（招行）";
            case 10:
                return "建行聚合支付";
            case 13:
                return "招行聚合支付";
            case 14:
                return "现金支付";
            case 17:
                return "招行一网通支付";
            case 24:
                return "ETC支付";
            case 26:
                return "畅行车";
            default:
                return "";
        }
    }

    private String caseExceptType(int exceptType) {
        switch (exceptType) {
            case 1:
                return "储值卡不扣费";
            case 2:
                return "储值卡扣费不符";
            case 3:
                return "储值卡扣费现金出场";
            case 4:
                return "自助缴费未出场";
            case 5:
                return "自助缴费现金出场";
            default:
                return "-";
        }
    }

    private String casePayFlag(Map<String, Object> item) {
        String type = "无费用出场";
        if (String.valueOf(item.get("outtime")).equals("0")) {
            type = "-";
        } else {
            if (String.valueOf(item.get("parkamt")).equals("0.00")) {
                type = "无费用出场";
            } else {
                if (String.valueOf(item.get("payflag")).equals("8")) {
                    type = "长租车";
                } else if (String.valueOf(item.get("payflag")).equals("1")) {
                    if (String.valueOf(item.get("chargeamt")).equals("0.00") && (Float.valueOf(String.valueOf(item.get("offline_coupon_amt"))) + Float.valueOf(String.valueOf(item.get("elec_coupon_amt")))) == 0) {
                        type = "无费用出场";
                    } else {
                        type = "现金";
                    }
                } else if (String.valueOf(item.get("payflag")).equals("5") || String.valueOf(item.get("payflag")).equals("9") || String.valueOf(item.get("payflag")).equals("10") || String.valueOf(String.valueOf(item.get("payflag"))).equals("11") || String.valueOf(item.get("payflag")).equals("12") || String.valueOf(item.get("payflag")).equals("20")) {
                    type = "电子支付";
                } else if (String.valueOf(item.get("payflag")).equals("23")) {
                    type = "ETC支付";
                } else {
                    if (String.valueOf(item.get("payflag")).equals("0")) {
                        type = "无费用出场";
                    } else {
                        type = "-";
                    }
                }
            }

        }
        return type;

    }

    public String paseMin(long mins) {
        long hours = mins * 60;
        long day = hours / (3600 * 24);
        long hour = (hours % (3600 * 24)) / 3600;
        long minu = ((hours % (3600 * 24)) % 3600) / 60;

        String retDay = day > 0 ? day + "天" : "";
        String retHour = hour > 0 ? hour + "时" : "";

        return retDay + retHour + minu + "分";


    }

    private void rollBack(LinkedHashMap<String, Object> map) {
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey();
            if (key.contains("member_info")) {
                itParkGroupMemberinfoService.removeById(entry.getValue().toString());
                QueryWrapper<TParkGroupOperateLog> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("group_id", entry.getValue().toString());
                itParkGroupOperateLogService.remove(queryWrapper);

                QueryWrapper<TCarMemberamountinfo> carMemberamountinfoQueryWrapper = new QueryWrapper<>();
                carMemberamountinfoQueryWrapper.eq("member_id", entry.getValue().toString());
                itCarMemberamountinfoService.remove(carMemberamountinfoQueryWrapper);
            }
            if (key.contains("carid_info")) {

                itParkGroupCarinfoService.removeById(entry.getValue().toString());
            }
            if (key.contains("seat_info")) {
                itParkGroupSeatinfoService.removeById(entry.getValue().toString());
            }
        }
    }

    private ResultJson checkReturn(List<Map<String, String>> list, LinkedHashMap<String, Object> map, String key) {
        String id = "";
        if (list.get(0).containsKey("id")) {
            id = list.get(0).get("id").toString();
        } else if (list.get(0).containsKey("group_id")) {
            id = list.get(0).get("group_id").toString();
        }

        if (StringUtils.isBlank(id)) {
            rollBack(map);
            return resultJson.setCode(ResultCode.FAIL).setMsg(ResultCode.FAIL_MSG).setData(null);
        } else {
            map.put(key, id);
            return null;
        }
    }


}
